How Bulk-Logged Recovery Model Works


It’s always been suggested / recommended to use Bulk-Logged Recovery model for Bulk Operation. But do we really know how Bulk-Logged Recovery model Works and what the risk associated to it.

Bulk Recovery model improves the performance of Certain Large Scale Bulk Operations; Such as Large BCP File (IN), Database Maintenance (Index Maintenance, DBCC CHECKS).


The bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

In case the log is damaged after the bulk-logged operation and no backup or transactional log backup is available, then all the changes need to be redone (data will be lost).


A good comparison has been provided in BOL


Recovery model


Work loss exposure

Recover to point in time?


No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Can recover only to the end of a backup.


Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, see Restoring a Database to a Point Within a Backup.

Bulk logged

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.

To track the data pages, a log backup operation relies on a bulk-changes bitmap page that contains a bit for every extent. For each extent updated by a bulk-logged operation since the last log backup, the bit is set to 1 in the bitmap. The data extents are copied into the log followed by the log data. The following illustration shows how a log backup is constructed.



Under the full or bulk-logged recovery models, until the first full backup, automatic checkpoints truncate the unused part of the transaction log, unless other factors are keeping log records active. After the first full backup, truncation requires backing up the transaction log. For information about factors that delay truncation,


Leave a Reply

Your email address will not be published.