Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Minimally Logging Bulk Load Inserts into SQL Server

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

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more

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.

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.

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

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.

Table Has Existing Data Has Clustered Index Has Non-Clustered Index Data Page Updates Index Page Updates
No No No minimally logged n/a
No No Yes minimally logged minimally logged
No Yes doesn't matter minimally logged minimally logged
Yes No No minimally logged n/a
Yes No Yes minimally logged fully logged
Yes Yes doesn't matter fully logged fully logged

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"

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:

next webcast button

next tip button

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

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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

@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:



Thursday, April 04, 2013 - 11:32:38 AM - Raghu Back To Top


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?

Wednesday, March 03, 2010 - 10:39:33 PM - bscharf Back To Top

 Great article!  Very useful!

Learn more about SQL Server tools