All about Bulk Logged

All about Bulk Logged

This document is relevant to bulk operations on SQL Server. The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging. This saves significantly on processing time, but prevents you from using the point-in-time restore option.

Microsoft recommends that the bulk-logged recovery model only be used for short periods of time. For best practice I had created a DB name called bulk Operations and Created couple of tables with two columns,

all_about_bulk_logged_1

I had executed the same kind of bulk insert operations twice and changing the recovery models as FULL and Bulk Logged. And captured the log file growth,

  • Initial size of Log File
  • After 8000 Rows Insertion [by using ordinary insert method]
  • Bulk Insert [by using select into clause]
  • bulk Insertion by using ordinary select
  • bulk Insertion by using BCP
  • bulk update
  • Create Index

all_about_bulk_logged_2

all_about_bulk_logged_3

Download the Scripts

Conclusion

Microsoft recommends the bulk-logged recovery model only is used for short periods of time. Its much suitable for processes like Select into clause, Index (Create / Rebuild/ Re-Organize) operations.

Reference

http://msdn.microsoft.com/en-us/library/ms190692.aspx

http://www.sql-articles.com/articles/troubleshooting/log-file-growth-in-sql-server

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Log file growth in SQL Server
Each database in SQL Server contains at least one data file and one transaction log file. The transaction log file stores the details of all the ...
Batch Script to Deploy Multiple SQL files (Version 2)
I took very long break to keep the site up to date with my learning. Few years back I wrote an article to deploy multiple scripts using batch fil...
Provisioning Azure Cosmos DB using Powershell
Azure Cosmos database is one of the NoSQL database that is available in Microsoft Azure cloud platform. Azure Cosmos db is getting more popular c...
powered by RelatedPosts