Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Minimally Logging Bulk Load Inserts into SQL Server

MSSQLTips author Greg Robidoux By:   |   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.

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"
  • 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
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server 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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.