The batch log recovery mode of the SQL Server recovery model.

The batch log recovery mode of the SQL Server recovery model.

Do you want to know why the transaction log file is getting bigger and bigger? Transaction logs are sometimes larger than your actual database files, especially in the case of application data warehouses. Why is this happening? How to control its size? How does the database recovery model control the growth of transaction logs? In this series of articles, we will provide answers one by one.

Batch log recovery mode

The batch log recovery mode is similar to the full recovery mode. It is expected that a large volume of data modification operations (such as index creation, select into, insert select, BCP, BULKINSERT) will be performed ), in this case, the log record volume can be minimized, reducing the performance impact. But at the same time, you may not be able to recover at any point in time. As a recommended practice, the batch log recovery mode can be used together with the full recovery mode. For example, you should set the full recovery mode in general operations, then, the batch log recovery mode is temporarily switched when a large number of operations occur occasionally. Finally, after a large number of operations are completed, return to the full recovery mode. If restoration is important at a time point, we recommend that you back up transaction logs after switching back to the full recovery mode.

Similar to the full recovery mode, transaction log files will continue to grow, so you need to back up transaction logs frequently. Without bulk operations, the batch log mode is the same as the full recovery mode. You can restore the data to any point in time as long as the transaction log contains all subsequent changes to the database.

Advantage: optimizes the performance of large volumes of operations by minimizing log records for some transactions. Transaction logs will not be significantly increased due to these large volumes of data operations.

Disadvantage: If the log is damaged or a large volume of data is performed after the recent log backup, data loss may occur. Therefore, changes after the last backup must be redone.

When to use: it is recommended to switch to the batch log recovery mode before the occasional batch data operations, and then switch back to the full recovery mode after the batch data operations are completed. In this way, you can still restore to any point in time, but your last transaction log backup does not contain a large volume of data operations, and you can also minimize the log volume of large volume data operations.

It should be noted that the minimal log record means to only record the information required to recover the transaction, and does not support time point recovery. In the case of minimal logs, transaction logs track the change records of large batches of data based on the batch change ing (MCP) page, rather than making logs for each change. In 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 for large-capacity Log recovery

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 onTransaction-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 (fully recording all transactions), the large-capacity log recovery mode only records the minimum number of large-capacity operations (although other transactions are fully recorded ). The large-capacity log recovery mode protects large-capacity operations from media faults and provides optimal performance and occupies the minimum log space.

However, the large-capacity log recovery mode increases the risk of data loss for these large-capacity replication operations, because large-capacity log operations prevent the re-capturing of changes made to each transaction one by one. If a log backup contains a large log volume, the log backup cannot be restored to the time point in the log backup, but the entire log backup can only be restored.

In the large-capacity log recovery mode, if the log backup overwrites any large-capacity operations, the log backup contains the log records and data pages changed by the large-capacity operations. This is critical to the result of capturing large-capacity log operations. The merged data zone makes log backup very large. In addition, backup logs need to access data files that contain large volumes of log transactions. If you cannot access any affected database files, the transaction log cannot be backed up and all operations committed in this log will be lost.
To track data pages, the log backup operation depends on the large-capacity changes on the bitmap page. The bitmap page contains one bit for each area. For each region updated by large-capacity log operations after 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. Shows the log backup construction method.

Important:

In full or large-capacity log recovery mode, if no other factors keep the log record active, when the first full backup is performed, the automatic checkpoint truncates unused parts of the transaction log. After the first full backup, the transaction logs must be backed up. For information on truncation latency factors, see factors that may delay log truncation.

SQL server 2000 supports three database recovery modes:

The administrator can select a backup process that has the least impact on the system at runtime and meets the restoration requirements. The Administrator also selects the database recovery mode based on the resource requirements. The recovery mode balances the overhead of records based on the importance of fully recovered data. Recovery modes include:

◆ Complete

Data is very important and must be restored to the fault point. Record all data changes. All recovery options of SQL Server 2000 can be used.

◆ Large-capacity log records

If necessary, some large-capacity operations (large-capacity copy operations, select INTO, text processing) can be replayed, so these operations are not fully recorded. It can only be restored to the end of the last database or log backup.

◆ Simple

All data changes made since the last backup can be replaced or redone. The record overhead is minimal, but the content after the last backup cannot be recovered.

SQL server 2005 only uses logs to restore the database. Only one database with the suffix of "log" is used to restore the database. How can this problem be solved?

Files suffixed with. mdf and. ldf are data files and log files respectively. Only these two files in the SQL SERVER2005 database can be recovered. In the object Resource Manager, right-click the "Database" node and use the "additional" function.