Minimally Logged Insert Into

Question

I have an INSERT statement that is eating a hell of a lot of log space, so much so that the hard drive is actually filling up before the statement completes.

The thing is, I really don't need this to be logged as it is only an intermediate data upload step.

For argument's sake, let's say I have:

• Table A: Initial upload table (populated using bcp, so no logging problems)
• Table B: Populated using INSERT INTO B from A

Is there a way that I can copy between A and B without anything being written to the log?

P.S. I'm using SQL Server 2008 with simple recovery model.

Answer 1

From Louis Davidson, Microsoft MVP:

There is no way to insert without logging at all. SELECT INTO is the best way to minimize logging in T-SQL, using SSIS you can do the same sort of light logging using Bulk Insert.

From your requirements, I would probably use SSIS, drop all constraints, especially unique and primary key ones, load the data in, add the constraints back. I load about 100GB in just over an hour like this, with fairly minimal overhead. I am using BULK LOGGED recovery model, which just logs the existence of new extents during the logging, and then you can remove them later.

The key is to start with barebones tables, and it just screams. Building the index once leaves you will no indexes to maintain, just the one index build per index.

If you don't want to use SSIS, the point still applies to drop all of your constraints and use the BULK LOGGED recovery model. This greatly reduces the logging done on INSERT INTO statements and thus should solve your issue.

http://msdn.microsoft.com/en-us/library/ms191244.aspx

Answer 2

Upload the data into tempdb instead of your database, and do all the intermediate transformations in tempdb. Then copy only the final data into the destination database. Use batches to minimize individual transaction size. If you still have problems, look into deploying trace flag 610, see The Data Loading Performance Guide and Prerequisites for Minimal Logging in Bulk Import:

Trace Flag 610

SQL Server 2008 introduces trace flag 610, which controls minimally logged inserts into indexed tables.