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.
- BULK INSERT
- INSERT INTO
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||Yes||minimally logged||minimally logged|
|No||Yes||doesn't matter||minimally logged||minimally logged|
|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 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: 2007-02-22
About the author
View all my tips