Batch log recovery model _mssql for SQL Server recovery models

Do you want to know why the transaction log files are getting bigger? Transaction logs can sometimes be even larger than your actual database files, especially if you are applying a data warehouse. Why is this happening? How do I control its size? How does the database Recovery model control transaction log growth? In this series of articles, we will answer each of them.

Bulk Log Recovery Model

The bulk log recovery model, like the full recovery model, is expected to have a large number of data modification operations (for example, to create an index, SELECT Into,insert Select,bcp,bulkinsert), in which case the amount of logging can be minimized, thereby reducing the performance impact. But at the same time the price is that you may not be able to do any point-in-time recovery. As a recommended practice, the bulk log recovery model can be used in conjunction with the full recovery model, for example, you should typically set the full recovery model during regular operations, and then temporarily switch to the bulk log recovery model when there are occasional bulk operations. Finally, after the bulk operation, then return to the full recovery model. If Point-in-time recovery is important, it is highly recommended that you do a transaction log backup after switching back to the full recovery model.

Like the full recovery model, transaction log files will continue to grow, so you need to do frequent transaction log backups. If there is no mass operation, the bulk log mode is the same as the full recovery model, and you can revert to any point in the transaction log, as long as it contains all the records of the changes made to the database.

Advantage: Optimize the performance of bulk operations by minimizing logging of some transactions. The transaction log does not grow significantly due to these bulk data operations.

Disadvantage: There is a possibility of data loss if the log is corrupted, or if large amounts of data operations occur after the most recent log backup. Therefore, changes since the last backup must be made again.

When to adopt: it is recommended to switch to the bulk log recovery model before the occasional mass data operation, and then switch back to the full recovery model after the bulk data operation is complete. In this way you can still recover to any point in time, just your last transaction log backups do not contain bulk data operations and can minimize the amount of log operations for bulk data.

Note that minimizing logging means that only the information needed for recovery transactions is logged, not point-in-time recovery is supported. In the case of minimizing the log, the transaction log is based on a large volume change mapping (MCP) page, instead of logging every change. This way the database log is smaller, but when you back up the transaction log, it includes all the change pages, so the transaction log backup may be larger even if the transaction log is very small.

Bulk-Logged Recovery model bulk_logged Recovery model

The bulk-logged recovery model minimally logs bulk operations, although fully other logging. The bulk-logged recovery model protects against media failure and, for bulk operations (Bcp,bulk to into), Insert,select Des the best performance and least log space usage.

The bulk-logged recovery model increases the risk of data loss for this bulk-copy operations, because bulk logging Ions prevents recapturing changes on a transaction-by-transactionbasis. If A log backup contains any bulk-logged operations, your cannot restore to a point-in-time within that log backup; yo u can restore only the whole log backup.

Bulk Changed Map (BCM) tracks the extents that have been modified through Bulk logged operations since the last Backu P LOG statement.
If using the bulk-logged recovery model, only details of the modified data pages are logged, allowing for better Performan Ce.

Tail Log backup
If Your database is using the bulk-logged recovery model, and the transaction log contains minimally logged ONS, the data files which contain the modified pages must also be available. If Those data files are unavailable, you are not being able to the tail of the transaction log. This is another the consider when using the bulk-logged recovery model.

However the situation with the bulk-logged recovery model are identical to the full recovery model if no Minimally logged transactions are created in the database

How the bulk-Logged recovery model works

In contrast to the full recovery model (full logging of all transactions), the bulk-logged recovery model only minimizes records for bulk operations (although other transactions are fully logged). The bulk-logged recovery model protects bulk operations from media failures, provides optimal performance, and consumes minimal log space.

However, the bulk-logged recovery model increases the risk of loss of data for these bulk-copy operations, because bulk-logged operations prevent the changes made to each transaction from being caught again. If a log backup contains bulk-logged operations, you cannot restore to the point in time in the log backup, only the entire log backup can be restored.

Under the bulk-logged recovery model, if a log backup overwrites any bulk operation, the log backup contains the log records and data pages that were changed by the bulk operation. This is critical for capturing the results of bulk-logged operations. The merged data area makes log backups very large. Additionally, the backup log requires access to the data file that contains the bulk-logged transaction. If you cannot access any of the affected database files, the transaction log will not be backed up, and all actions committed in this log are lost.
To track data pages, log backup operations rely on bulk changes to bitmap pages, and bitmap pages contain one bit for each zone. For each area that has been updated by bulk-logged operations since the last log backup, set each bit to 1 in the bitmap. The data area is copied to the log, followed by the log data. The following figure shows how log backups are structured.

Important NOTE:

Under the full or bulk-logged recovery model, if there are no other factors that keep logging active, automatic checkpoints truncate the unused portion of the transaction log until the first full backup. After the first full backup, truncation requires that the transaction log be backed up. For information about the truncation latency factor, see the factors that may delay log truncation.