SQL Server 2008 Minimally Logged Inserts

SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the requirements below are met.

• Trace flag 610 must be on
• Database recovery model must be bulk-logged or Simple
• Inserted data must be ordered by the clustered index

To turn on the trace flag for your current session:

[cc lang=”sql”]
DBCC TRACEON (610)
INSERT INTO dbo.MyTable
SELECT * FROM
ORDER BY 1
DBCC TRACEOFF (610)
[/cc]

This new change differs dramatically from the previous requirements for minimal logging. Previously there could be no clustered index and a table lock had to be acquired on the target table.

For more information, visit:
Minimal Logging Changes – MSDN Blog