Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about when minimal logging does or does not occur.

Using this script I find that the sum of the Log Record Lengths for each of the different types of inserts is the following:

• Heap empty no tablock 60000

• Heap empty with tablock 56000

• Heap non empty no tablock 60000

• Heap non empty with tablock 56000

• Heap plus index empty no tablock 126188

• Heap plus index empty with tablock 114188

• Heap plus index non empty no tablock 138696

• Heap plus index non empty with tablock 112000

• Cluster empty ordered no tablock 64168

• Cluster empty ordered with tablock 56168

• Cluster empty unordered no tablock 73388

• Cluster empty unordered with tablock 65388

• Cluster non empty no tablock 63912

• Cluster non empty with tablock 55944

• Cluster plus index empty no tablock 124336

• Cluster plus index empty with tablock 108336

• Cluster plus index non empty no tablock 123876

• Cluster plus index non empty with tablock 107924

A few of these numbers do not seem to match the table on the technet page. In particular:

• There seems to be no difference in logging between inserting into empty vs non empty tables, but the page claims there should be full logging when inserting into a non empty cluster without tablock

• Inserting with tablock into a heap or cluster with and index does seem to reduce logging, but the page claims there should be full logging.

• When using the SELECT INTO method of insert, there are no rows in fn_dblog whose operation is insert, but the page lists this method as a bulk load operation that should have the behavior described in the table

For reference, this was run on a SQL express database, and when I run DBCC TRACESTATUS (610) everything is 0.

Can anyone help to explain why I might be seeing these discrepencies?

For reference the code is bel

A few of these numbers do not seem to match the table on the technet page.

There are small differences in the sizes of the log records generated in your tests, but these are due to other internal logging behaviours, not whether minimal logging is occurring or not.

A good definition of minimal logging is provided by Sunil Agarwal of the Storage Engine team:


Individual rows are not logged and only the changes to page allocation structures are logged

Any test where you see individual row changes logged (e.g. LOP_INSERT_ROWS) is not using minimal logging for the associated allocation unit. Some operations can be minimally logged with respect to one allocation unit (e.g. an index) and not minimally logged against another. Also, in some circumstances, inserts to existing pages may not be minimally-logged but changes to newly allocated page may be.

Most of the details can be found in a series of Storage Engine team blog posts:

• Bulk Import Optimizations (Minimal Logging)

• Minimal Logging changes in SQL Server 2008

• Minimal Logging changes in SQL Server 2008 (part-2)

• Minimal Logging changes in SQL Server 2008 (part-3)

One detail not explored there is that to be minimally-logged (in SQL Server 2008 or later) INSERT...SELECT changes to b-tree structures must have the DMLRequestSort query plan operator property set to true. This applies to the circumstances where the Data Loading Performance Guide shows 'Depends': the query plan must use wide (per-index) maintenance with DMLRequestSort=true.

I wrote more about this in Minimal Logging with INSERT…SELECT and Fast Load Context.

StackExchange Database Administrators Q#52736, answer score: 13