All about Bulk Logged

Written by Siva. Posted in General

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

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)
All about Bulk Logged, 5.0 out of 5 based on 1 rating

Tags: , ,

Trackback from your site.

Leave a comment

*

Recent Comments

Amit Bhatt

|

Hi Deepak,

Thanks for such a nice article.

You missed one thing to add in code:
@article = ‘all’,

Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’

Error 18486 | Platformblog

|

[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]

VidhyaSagar

|

Naveen,
I’ll check this out and get back to you.

balakiran

|

Thanks man, Very simple & easy to understand !!!!