Talking about transaction log in SQL Server (iv)----The role of the log in the full recovery model

This article is a series of articles in the fourth and the last, this article requires the first three articles of knowledge as the basis, the first three articles address as follows:

On transaction log in SQL Server (i)----the physical and logical architecture of transaction logs

Talking about transaction log in SQL Server (ii)----The role of the transaction log when modifying data

Talking about the transaction log in SQL Server (iii)----The role of the log in the simple recovery model

Introduction

The data in the production environment, if it can be written on the balance sheet, I think the amount of this asset will not be small. And Murphy's law, if it's possible to get bad, no matter how small the odds are, it seems to be tailored to the DBA. Under the simple recovery model described in the previous article, there is a risk of loss from the most recent backup to the current data. The full backup model reduces the risk of data loss significantly. This article focuses on the conceptual principles and the role of logging in the full backup mode.

Complete (full) recovery mode

The full recovery model gives the data maximum protection by logging any modifications to the database. Under the full recovery model, the log does not only guarantee the acid of the database transaction. You can also restore the data to any point in time within the log range.

As stated in the previous article, in the simple recovery model, logs are almost not managed. It is possible for each checkpoint to truncate the log to reclaim inactive VLF in order to reuse space. Therefore, in the simple recovery model, the space usage of the log can hardly be considered. In contrast, in the full recovery model, logs are an important part of recovering data, and the management of logs and the management of log space usage require attention.

Under the full recovery model, Checkpoint does not truncate the log. Only a backup of the log pushes the MinLSN backwards and truncates the log. So in a system with a slightly larger volume of traffic, the log will grow fast.

Therefore, the purpose of log backup is divided into the following two:

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

By being excerpted from MSDN, you can see:

A full backup was made at Db_1, and two log backups (log_1 and Log_2) were made in the next two times, and the server was damaged by the disk in which the data was located shortly after the Log_2 was backed up. At this point, if the log file is intact, you can restore the database to the point in time of the disaster by backing up the tail log (Tail of log), recovering from db_1, and then resuming log_1,log_2. Theoretically, the loss of data can be 0.

The principle of recovering data from a log is redo, which is to redo the transactions recorded in the log. This overhead is much larger than recovering from a full or differential backup. Therefore, as much as possible, reduce the amount of recovery using logs. Instead, use full or differential backups to recover more data.

Bulk-Logged (bulk-logged) recovery Model

The bulk recovery model is the same in many places as the full recovery model. However, because under the full recovery model, every operation on the database is recorded in the log. Import and export operations for some large amounts of data will undoubtedly leave a large number of records in the log. In many cases, we do not need to record this information in the log.

The bulk-logged recovery model is an alternative to the full recovery model. Microsoft's recommended best practice is to temporarily switch from the full recovery model to the bulk recovery model to save logs when doing a lot of data operations, such as index creation and rebuilt,select into operations. This conversion does not break the log chain.

This article does not delve into this pattern, just a simple explanation of the concept. Assuming that I want to insert a batch of data, the following information is logged in the full recovery model and bulk-logged recovery model:

As you can see, in the log, the bulk recovery model transforms this type of operation into an atom.

log Chain (Chain)

A continuous log backup is called a log chain. Indicates that the log is contiguous. This concept can be expressed as:

Assuming that the above two log backups can be simply abstracted as 2 backups, the end LSN of log backup 1 must be less than or equal to the first LSN of log backup two (typically the first end LSN equals the first LSN of the second log backup, but because the "back up log only" option exists to back up the log, The log is not truncated, so it is possible to overlap). The log chain for the two backups is sequential.

is an example of a production environment that looks at the log chain continuum in SSMs:

As you can see, after the first full backup, the transaction log is backed up multiple times, and the start LSN of each transaction log equals the end LSN of the previous transaction log. Therefore, you can restore to any point in time during the last log backup, starting with the first full backup.

The complete log chain begins with the first full backup or from the simple recovery model to full or bulk-logged mode, ending at the current point in time.

Recovering data from the log requires that the log chain from the most recent full or differential backup to the recovered point of time is contiguous.

Recovery Order

Recovering data from a backup takes the following steps:

1. Replicate data phase: Copy data, index pages, and logs from full and differential backups to the recovered database file.

2.Redo (roll forward) phase: Applies transactions recorded in the log to data copied from the backup. Forward the data roll to a specified point in time. Once this phase is complete, the database is still in a non-available phase:

:

The top two is restore.

3.Undo (Roll back) stage: This is also the legendary recovery, rollback any uncommitted transactions. After this phase, the database is in a usable state. Any subsequent backups will not be applied to the current database.

This concept such as:

In a continuous log backup of two log chains, the transaction defined in the first transaction log backup commits in the second transaction log backup. If the recovery option is used after the first transaction log is restored. That was the undo phase. Then transaction 1 is rolled back during the undo phase:

As can be seen, the T1 in log backup 1 is rolled back, and the commit in log backup 2 is meaningless. This is why it is not allowed to resume subsequent backups after the undo phase has passed. Therefore, Microsoft recommends that the best practice is to use the NORECOVERY option without the undo phase. The undo phase is performed separately after all backups have been restored, which can be done by specifying the recovery option when the tail of the log is restored.

Talking about transaction log in SQL Server (iv)----The role of the log in the full recovery model