Minimally Logging Bulk Load Inserts into SQL Server

By: Greg Robidoux   |   Read Comments (3)   |   Related Tips: More > Import and Export

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

Last Update: 2/22/2007

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

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

Thursday, April 04, 2013 - 11:32:38 AM - Raghu	Read The Tip
Hi, I want to import data from SQL Server 2008 to Excel 2007 through ASP.Net 4.0 Tool with the help of C#.net coding. Can you please help me?

Thursday, April 04, 2013 - 3:17:36 PM - Greg Robidoux

Read The Tip

@Raghu - I am sure there are several other ways to do this via .NET, but you can take the same exact concepts in this tip and use the SQL commands to import the data.  The biggest thing you will need to worry about is security to make sure each component has the permissions needed to read and import the data. Look at this other tip too: http://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/

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 Signup for our newsletter

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

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. The SQL Server Security THREAT - It’s Closer Than You Think