27 April 2012 13:04
I'm currently moving some large tables (>10GB, >10Mio rows) from one database to another database. Both databases are running in the same SQL Server (9.0.3068) instance. The destination databases logging is set to bulk-logging. Copying the first three tables - INSERT INTO DestDB.Schema.Table ( Columns ) SELECT Columns FROM SourceDB.Schema.Table worked like charme. Copied ~50GB with the log grown to a maximum size of ~50GB. Now I'm trying the same, but the log is exploding. Thus growing to maximum fixed size of 132GB, initial log size when starting the transaction was 2GB. And, yeah, automatic rollback.
I have no clue what's going on. Any hint is welcome..
27 April 2012 14:02I was just taking a look at the requirments for bulk-logged transactions and saw that the target table must be a heap -- no clustered index. http://msdn.microsoft.com/en-us/library/ms174335.aspx On that article, scroll down to Using "INSERT INTO…SELECT to Bulk Import Data with Minimal Logging"
27 April 2012 14:07
Then I have a problem with the first three copied tables: They have all an additional clustered index, it's always not the primary key..
27 April 2012 14:18
27 April 2012 14:46No, I've copied the the first three tables in one session.
27 April 2012 15:03
I was just taking a look at the requirments for bulk-logged transactions and saw that the target table must be a heap -- no clustered index. http://msdn.microsoft.com/en-us/library/ms174335.aspx On that article, scroll down to Using "INSERT INTO…SELECT to Bulk Import Data with Minimal Logging"
Sorry to step in but INSERT INTO wit SELECT can be minimally logged but that is only starting with SQL 2008; I believe Stefan is using SQL 2005.
27 April 2012 15:17
Good catch! So, my advice would be to just one run of these inserts at a time and do a transaction log backup after each one.
- Ditandai sebagai Jawaban oleh Olaf HelperMicrosoft Community Contributor, Moderator 13 Mei 2013 18:38
27 April 2012 15:27
Yes, I concur with you opinion.
Stefan, how about issuing a periodic T-Log backup in between?
I understand previous three tabels copied fine but any log backup in between.
30 April 2012 9:08Okay, I need to retest it. Thanks so far.