Batch log recovery model of SQL server recovery model

Time:2021-6-9

Do you want to know why the transaction log file is getting bigger and bigger? Sometimes the transaction log is even larger than your actual database file, especially in the case of data warehouse application. Why does this happen? How to control its size? How does database recovery model control transaction log growth? In this series of articles, we will give the answers one by one.

Batch log recovery mode

The batch log recovery mode is similar to the full recovery mode. It is expected that there will be a large number of data modification operations (for example, index creation, select into, insert select, BCP, bulk insert). In this case, the amount of logging can be minimized, so it reduces the performance impact. But at the same time, the price is that you may not be able to recover at any time. As a recommended practice, the bulk log recovery mode can be used in conjunction with the full recovery mode. For example, you should usually set it to the full recovery mode during regular operations, and then temporarily switch to the bulk log recovery mode when there are occasional large-scale operations. Finally, after a large number of operations, we return to the full recovery mode. If point in time recovery is important, we highly recommend a transaction log backup after switching back to full recovery mode.

Similar to the full recovery model, transaction log files will continue to grow, so you need to make frequent transaction log backups. If there is no mass operation, the batch log mode is the same as the full recovery mode. You can recover to any time point, as long as the transaction log contains all the subsequent changes to the database.

Advantages: optimize the performance of mass operations by minimizing logging of some transactions. The transaction log will not grow significantly due to these massive data operations.

Disadvantages: if the log is damaged, or a large number of data operations occur after the latest log backup, there is the possibility of data loss. So changes since the last backup must be redone.

When to use: it is recommended to switch to the bulk log recovery mode before the occasional mass data operation, and then switch back to the full recovery mode after the mass data operation. In this way, you can still recover to any point in time, except that your last transaction log backup does not include mass data operations, and at the same time, you can minimize the log volume of mass data operations.

It should be noted that minimizing logging means that only information needed to recover transactions is recorded, and point in time recovery is not supported. In the case of minimizing the log, the transaction log records the page track of mass data changes based on the mass change mapping (MCP) page, rather than logging each change. This way, the database log will be smaller, but when you back up the transaction log, it includes all the change pages, so even if the transaction log is very small, the transaction log backup may be larger than it.

Large capacity log recovery mode bulk_ logged recovery model

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(bcp,BULK INSERT,SELECT INTO), provides the best performance and least log space usage.

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

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

Tail Log backup
If your database is using the bulk-logged recovery model, and the transaction log contains minimally logged transactions, the data files which contain the modified pages must also be available. If those data files are unavailable, you will not be able to back up the tail of the transaction log. This is another point to consider when using the bulk-logged recovery model.

However, please note that the situation with the bulk-logged recovery model is identical to the full recovery model if no minimally logged transactions are created in the database

Working principle of large capacity log recovery mode

Compared with the full recovery mode, which records all transactions completely, the bulk log recovery mode only records the smallest amount of bulk operations (although other transactions are fully recorded). The large capacity log recovery mode protects large capacity operations from media failure, provides the best performance and occupies the minimum log space.

However, the bulk log recovery model increases the risk of data loss for these bulk copy operations because bulk log operations prevent the capture of changes made to each transaction one by one again. If the log backup contains large volume log operations, it cannot be restored to the time point in the log backup, but can only restore the whole log backup.

In the bulk log recovery mode, if the log backup covers any bulk operations, the log backup contains the log records and data pages changed by the bulk operations. This is crucial for capturing the results of bulk logging operations. The merged data area can make the log backup very large. In addition, the backup log needs to access the data file containing large volume log transactions. If any of the affected database files cannot be accessed, the transaction log cannot be backed up and all operations committed in this log are lost.
In order to track the data pages, the log backup operation depends on the large capacity changes of the bitmap page, which contains one bit for each area. For each area updated by bulk logging 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 the log backup is constructed.

Important note:

In the full or bulk log recovery mode, if there is no other factor to keep the log records active, the automatic checkpoint will not truncate the unused part 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 truncation delay factors, see factors that may delay log truncation.