Bulk-Logging woes

Question

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..

Answer 1

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"

---

Kathi Kellenberger

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 2

Thanks.

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..

Answer 3

Have you done a transaction log backup? They are still required even if the recovery model is set at bulk logged.

---

Kathi Kellenberger

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 4

No, I've copied the the first three tables in one session.

Answer 5

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"

---

Kathi Kellenberger

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

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.  

Answer 6

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. 

---

Kathi Kellenberger

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 7

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. 

Answer 8

Okay, I need to retest it. Thanks so far.