Minimal logging for bulk inserts?

Question

I have many scripts running that truncate entire tables and rewrite data daily, however this fills my transaction logs very quickly. All these scripts use INSERT INTO .. SELECT. So recently I've changed my database to the Bulk-Logged recovery model, and I added the with (TABLOCK) to all my insert statements. This doesn't seem to be doing anything, because according to https://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/ if the table has a clustered or non-clustered index, it still gets fully logged. Should I then drop all the indexes before inserting, and recreate them after the table is populated? Or is there a better method to do this?

Answer 1

Paul White has a really detailed post about a way to get minimal logging for INSERT...SELECT statements that target clustered tables with secondary indexes. Check out the full article here:

Minimal Logging with INSERT…SELECT and Fast Load Context

The main thing you're probably missing is that you need to enable trace flag 610.

Read the whole thing to see make sure you get past all the conditions and edge cases - especially around estimated number of rows to be inserted, existing number of pages in indexes, and the estimated data size of rows being inserted.