Transaction logs in SQL Server (iv) -- Role of logs in full recovery mode

This articleArticleThis is the fourth and last article in the series. This article requires the knowledge of the first three articles as the basis. The addresses of the first three articles are as follows:

Transaction logs in SQL Server (I)-physical and logical architecture of transaction logs

Transaction Log in SQL Server (2)-Role of transaction log in data modification

Transaction Log in SQL Server (III)-Role of log in simple recovery mode

 

Introduction

If the data in the production environment can be written on the balance sheet, I think the amount of this asset will not be small. Murphy's Law (if something goes worse, no matter how small it may be) seems to be tailored to DBAs. In the simple recovery mode described in the previous article, data from the last backup to the current may be lost. The full backup mode greatly reduces the risk of data loss. This topic describes the concept and concept of log in full backup mode.

 

Full recovery mode

The full recovery mode maximizes data protection by recording any changes to the database to logs. In the full recovery mode, logs are not only used to ensure acid of database transactions. Data can also be restored to any time point within the log range.

As mentioned in the previous article, in simple recovery mode, logs are hardly managed. Each checkpoint may truncate the log to reclaim the inactive VLF for reuse of space. Therefore, in simple recovery mode, the use of log space can be hardly considered. On the contrary, in the full recovery mode, logs are an important part of data recovery. Therefore, you must pay attention to log management and management of log space usage.

In full recovery mode, the checkpoint does not truncate logs. Minlsn is pushed back and truncated only when logs are backed up. Therefore, in a system with a slightly larger business volume, the log growth rate will become very fast.

Therefore, the purpose of log backup is as follows:

• Reduce activity log size
• Reduces the risk of log corruption

You can see from the excerpt in msdn:

 

A complete backup was made at db_1, and two log backups (log_1 and log_2) were made respectively for the next two times. Shortly after the log_2 backup, the server was damaged due to the disk where the data was located. In this case, if the log file is in good condition, you can back up the tail log and restore log_1 and log_2 from db_1, tail logs are used to restore the database to the time point when a disaster occurs. Theoretically, the loss of data can be set to 0.

The principle of restoring data from a log is redo, that is, redo the transactions recorded in the log. This overhead is much larger than recovering from a full or differential backup. Therefore, reduce the Log Recovery volume as much as possible. Instead, use full or differential backup to restore more data.

 

Bulk-logged recovery mode

The large capacity recovery mode is the same in many places as the full recovery mode. However, in the full recovery mode, each operation on the database is recorded in the log. For some import and export operations on a large amount of data, there will undoubtedly be a large number of records in the log. In many cases, we do not need to record this information in the log.

The large-capacity log recovery mode is an alternative to the full recovery mode. The best practice recommended by Microsoft is to temporarily switch from the full recovery mode to the large-capacity recovery mode to save logs when performing a large amount of data operations (such as index creation, rebuilt, and select into operations. This conversion will not damage the log chain.

This article will not discuss this model in depth, but will simply explain this concept. Assume that you want to insert a batch of data, the complete recovery mode and the large-capacity log recovery mode are recorded in the log as follows:

It can be seen that in the log, the large-capacity recovery mode changes such operations into an atom.

 

Log chain)

Continuous log backup is called a log chain. Indicates that logs are continuous. This concept can be expressed as follows:

Assume that the preceding two log backups can be simply abstracted as the preceding two backups, then, the lsn at the end of log Backup 1 must be greater than or equal to the first lsn of log backup 2 (generally, the lsn at the end of the first log is equal to the first lsn of the second log backup, however, because the "only backup log" option only backs up logs and does not truncate logs, it may overlap ). The log chains of the two backups are continuous.

Is an example of viewing the log chain continuity in SSMs in a production environment:

It can be seen that after the first full backup, the transaction log is backed up multiple times. The starting lsn of each transaction log is equal to the ending lsn of the previous transaction log. Therefore, it can be recovered from the first full backup to any time point during the last log backup.

 

The complete log chain starts from the first full backup or from the simple recovery mode to the full or large-capacity log mode and ends at the current time.

The log chain from the last full or differential backup to the time point after recovery is consecutive.

 

Recovery order

Perform the following steps to recover data from backup:

1. Data replication phase: Copy data, index pages, and logs from the complete backup and differential backup to the recovered database file.

2. Redo (Roll Forward) stage: Apply the transactions recorded in the log to the data copied from the backup. Make the data roll forward to the specified time point. After this stage is completed, the database is still in the unavailable stage:

:

The above two parts are restore

3. Undo (Roll Back) stage: this is also the legendary recovery, which rolls back any uncommitted transactions. After this stage, the database is available. Any subsequent backup cannot be applied to the current database.

This concept is as follows:

For two consecutive log backups, the transaction defined in the first transaction log backup is committed in the second transaction log backup. if the recovery option is used after the first transaction log is restored. that is, it has gone through the Undo stage. Transaction 1 will be rolled back in the Undo phase:

It can be seen that T1 in log Backup 1 is rolled back, and commit in log backup 2 is meaningless. This is why subsequent backup cannot be restored after the Undo phase. Therefore, the best practice recommended by Microsoft is to use the norecovery option without performing the Undo phase. The undo phase is performed independently after all backups are restored. This operation can be performed by specifying the recovery option when restoring the log tail.

 

Summary

This article briefly introduces the functions of logs and the concepts of data recovery in the complete recovery mode. Understanding the concept of a full recovery model is irreplaceable to reduce the risk of data loss.