Minimal Logging Basics and SQL Server 2008 Enhancements

SQL Server has a variety of features that let developers and DBAs take advantage of minimally logged inserts and leverage the Bulk Copy API. This is an extremely important area to understand when doing large data loads or moving data between tables, databases, or servers, but I have found that many DBAs don’t understand what’s actually happening under the covers, and/or don’t realize that taking full advantage of these interfaces requires playing by a couple of special rules.

In short:

1. There is no such thing as a “non-logged” transaction in SQL Server (a very common myth)
2. Minimal logging means that only extent allocations will be logged, rather than the actual data copied to the destination. This is a huge plus when moving enough data to fill many new pages… but again, it’s not a lack of logging, just less logging
3. In order to take advantage of minimal logging (as of SQL Server 2005), the bulk copy operation must use a table lock, and the table must have either no rows or no indexes

For more detail on all of this, please see the following two recent posts from the SQL Server Storage Engine blog:

• What are the Bulk Import Optimizations?
• Bulk Import Optimizations (Minimal Logging)

And all of that is great, but it’s not what prompted me to post today.

Background: SQL Server has long had a feature called SELECT INTO that lets you do a minimally logged insert via a SELECT, but it requires that the target table does not exist (it will be created by the SELECT). This feature is a bit limiting; you can’t target a specific filegroup, you need to use somewhat awkward CASTs or CONVERTs to make sure the target columns have the right scale/precision (if you’re working with variable-length types), and you can’t pre-create indexes or constraints.

After spending a long time on a project for a client that does a huge amount of data movement using SELECT INTO, I posted a Connect item asking for an enhancement where a minimally-logged insert would be available from a SELECT doing an INSERT into an existing table. And I waited. And I waited some more. And then one day the item was updated with a comment: “This is targetted to be available in SQL Server 2008.”

I wasn’t sure exactly what this meant until the last few days, when Sunil Agarwal posted a series of three posts (one, two, three) describing the enhancements to minimal logging in SQL Server 2008 — including the ability to do a minimally logged insert via a SELECT, to an existing table!

This is a fantastic enhancement, and one that I am certain some of my customers will be able to make great use of. I have been kind of lukewarm on many of the SQL Server 2008 enhancements, but this one goes immediately into my top 5 and is one of those things I will bring out when certain customers ask me whether it’s worth their time to upgrade. Cheers to the Storage Engine team for making this happen!