Minimally Logging Bulk Load Inserts into SQL Server

By: Greg Robidoux | Read Comments (1) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

Problem
One of the advantages of using the Bulk-Logged recovery model is to minimally log bulk load commands, but still have other transactions fully logged.  You may find that when you set your recovery model to Bulk-Logged or to Simple and you run your bulk load commands that the size of your transaction log still grows quite large.  The reason for this is that there are some other settings and criteria that need to be met in order to minimally log the bulk insert commands and to minimize the amount of space needed in your transaction log.

Solution
When bulk loading data into SQL Server and to minimally log the transaction you need to first set your database to either the Bulk-Logged or Simple recovery model.  Once this is done you can take advantage of minimally logging the transaction by doing the following.

Commands
These are the bulk load commands that allow you to take advantage of having the transactions minimally logged. In addition, you can use DTS or SSIS to bulk load data which basically invokes these same techniques.

• bcp
• BULK INSERT
• INSERT INTO

Criteria
In addition to the commands above that allow you to minimally log the transaction, the following criteria must also be met. 

• Table is not replicated
• Table locking is used (see how to set locks below)

There are two operations that get logged, data page updates and index page updates.  The following chart shows you when and how things will be logged in the transaction log when issuing a bulk load command.

Note: If a table has a clustered index and has data, this operation is fully logged regardless of the recovery model.

How to set Table Locks
Table locking can be initiated a few different ways.  Either with the bulk load command or across the board for the table.

To issue this across the board for a table use the sp_tableoption stored procedure.  For example let's say we want to turn on table lock on bulk load for table Contacts, you would issue the following command:

sp_tableoption dbo.Contacts, 'table lock on bulk load', 1

Once this is set on all bulk load operations will use the table lock option by default.

If you want to turn it off issue the following command:

sp_tableoption dbo.Contacts, 'table lock on bulk load', 0

In addition, you can specify the table lock hint with the bulk load commands as follows:

• bcp ... -h "TABLOCK"
• BULK INSERT ... WITH TABLOCK
• INSERT ... SELECT * FROM OPENROWSET(BULK...)

Setting table locks from DTS
To turn this on when loading data via DTS and the Transform Data Task.

To turn this on when loading data via DTS and the Bulk Insert Task.

Setting table locks from SSIS
To turn this on when loading data via SSIS and the Bulk Insert Task.

To turn this on when loading data via SSIS and the Data Flow Task. This shows you the properties of the Data Flow components.

Next Steps

• Next time you need to bulk load data check the criteria found above and the table lock option to take advantage of minimally logging the bulk load operation
• Check your existing jobs to see if they are setup correctly and that they are taking advantage of this feature

Related Tips: More | Become a paid author

Last Update: 2/22/2007

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Wednesday, March 03, 2010 - 10:39:33 PM - bscharf	Read The Tip
Great article!  Very useful!

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! SQL Monitor – For database professionals who need results on Day One. Try it online. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Get SQL Server Tips Straight from Kevin Kline. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think