Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to batch log recovery model in SQL Server. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Bulk log recovery model

The bulk log recovery model is similar to the full recovery model in that it expects a large number of data modification operations (for example, index creation, SELECT INTO,INSERT SELECT,BCP,BULKINSERT), in which 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 point in time. As a recommended practice, the bulk log recovery model can be used with the full recovery model, for example, you should usually set it to the full recovery model during regular operations, and then temporarily switch to the bulk log recovery model when occasional bulk operations occur. Finally, after completing the mass operation, we return to the full recovery model. If point-in-time recovery is important, we highly recommend doing a transaction log backup after switching back to the full recovery model.

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 bulk operation, the bulk log model is the same as the full recovery model, and you can recover to any point in time, as long as the transaction log contains all subsequent changes made to the database.

Pros: optimize the performance of bulk operations by minimizing logging of some transactions. Transaction logs are not allowed to grow significantly as a result of these bulk data operations.

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

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

It is important to note that minimizing logging means that only the information needed to recover the transaction is recorded and point-in-time recovery is not supported. In the case of minimizing the log, the transaction log records the page track based on the bulk data 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.

Bulk-logged recovery model 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 atransaction-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 backupIf 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

How the bulk-logged recovery model works

Compared to the full recovery model (where all transactions are fully logged), the bulk-logged recovery model records only bulk operations to a minimum (although other transactions are fully logged). The bulk-logged recovery model protects high-volume operations from media failures, provides the best performance, and takes up minimum log space. However, the bulk-logged recovery model increases the risk of data loss in these bulk-logged operations because bulk-logged operations prevent changes made on a transaction-by-transaction basis from being captured again. If a log backup contains bulk log 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 the log backup overrides any bulk operation, the log backup contains log records and data pages that have been changed by the bulk operation. This is critical to capture the results of bulk logging operations. The merged datazone can make log backups very large. In addition, backing up the log requires access to data files that contain bulk log transactions. If any affected database files cannot be accessed, the transaction log cannot be backed up and all operations committed in this log are lost. To track data pages, log backup operations rely on bulk changes to bitmap pages, which contain one bit for each extent. For each zone that has been updated by bulk log operations since the last log backup, each bit is set 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 constructed.

Important tips:

Under the full or bulk-logged recovery model, if there are no other factors that keep the log active, the automatic checkpoint will not truncate the unused portion of the transaction log until the first full backup. After the first full backup, truncation requires a backup of the transaction log. For information about truncation delay factors, see factors that may delay log truncation.

This is the end of how to share the batch log recovery model in SQL Server. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.