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

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>