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

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