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,


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



Download the Scripts


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.






Leave a Reply

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