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

This article is the third in a series of articles, the first two addresses are 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

Introduction

Under the simple recovery model, the role of log files is simply to guarantee the ACID properties of SQL Server transactions. Does not assume the role of specific recovery data. As the word "simple" literally means, backup and recovery of data is only dependent on manual backup and recovery. Before you begin the article, you should first understand the different types of backup that SQL Server provides.

Several types of backup provided by SQL Server

Several of the backup types provided by SQL Server can be divided into the following three categories (file and filegroup backups and partial backups are not covered in this article):

1. Full backup: Replicate all areas (Extent) of the backed up data directly. Here are 2 points worth noting:

• A full backup does not back up all parts as its name "full", but instead backs up only the database itself, not the log (although only a small number of logs are backed up for synchronization)
• Full backup the database is available during the backup. A full backup records the MINLSN number at the start of the backup, the LSN number at the end of the backup, the log for that interval, and the restored database when it is restored (modified here, thanks to the six points of magic)

2. Differential (differential) Backup: Only after the last full backup is backed up, make the modified part. The backup unit is a zone (Extent). means that even if only one page has been changed in a zone, it will be reflected in the differential backup. Differential backups depend on a bitmap for maintenance, a bit corresponds to a zone, and since the last full backup, the modified area is set to 1, while the corresponding zone in bitmap is backed up by a differential backup. After the next full backup, all the bits in the bitmap are reset to 0.

3. Log backup: Backs up only the records since the last full or log backup. In simple mode, log backups are meaningless (SQL Server does not allow you to back up logs under the simple Recovery Model), and the following explains why log backups do not make sense under the simple recovery model.

Easy Recovery Model (simple Recovery mode)

Under the simple recovery model, the log is only meant to guarantee the acid of SQL Server transactions. There is no function to recover the data.

For example, we have a backup plan, as follows:

We make a full backup at 10 o ' 0 o'clock every week, and make a differential backup for the Wednesday and Friday 0 o'clock respectively. Under the simple recovery model, if the Saturday database crashes. Our recovery plan is only restored based on a full backup of week 10, and then a differential backup of Friday 0 o'clock. All data will be lost during the Friday 0 o'clock to the server crash.

As the word "simple" covers, the log can be completely out of management under the simple recovery model. Backup and recovery are completely dependent on our own full and differential backups.

The recovery model is a database-level parameter that can be configured either in SSMS or through an SQL statement:

space usage of logs in simple recovery mode

As mentioned in the first article in this series, the log files are divided into multiple VLF for management, logically logging is linear, giving each record a sequential, unique LSN.

In the simple recovery model, the data that is likely to be rolled back is written to the log in order to ensure the persistence of the transaction. These logs need to be temporarily saved in the log to ensure that transactions can be rolled back smoothly under certain conditions. This involves a concept-the minimum recovery LSN (Minimum Recovery LSN (MinLSN))

MinLSN is the smallest LSN number in the log where the transaction is not yet closed, and MINLSN is the minimum of one of the following three:

• Start LSN of checkpoint

• Minimum LSN of a transaction that is not yet closed in the log

• LSN of the origin of the oldest replication transaction that has not been delivered to the distribution database.

is a fragment of a log:

(Photo excerpt from MSDN)

As can be seen, the latest LSN is 148,147 checkpoint, before this checkpoint transaction 1 is completed, and transaction 2 is not completed, so the corresponding MINLSN should be the beginning of transaction 2, that is, 142.

From MINLSN to the logical end of the log, it is called the active log.

The relationship between the activity log distribution on the physical VLF can be expressed as:

Therefore, the status of VLF is derived from the state of the LSN it contains, and can be divided into two main categories: active VLF and inactive VLF

More subdivision can divide the status of VLF into the following four categories:

1. Active – when any LSN stored on VLF is active, the VLF is active, even if a 200M VLF contains only one LSN, such as the VLF3
2. Recoverable (recoverable) –VLF is inactive, the VLF does not contain an active LSN but has not been truncated (truncated)
3. Reusable (REUSABLE) –VLF is inactive, the VLF contains no active LSN, has been truncated (truncated), can be reused
4. unused (Unused) –VLF is inactive and has not been used

Concepts such as:

The so-called truncation (truncated) simply transforms the VLF of the recoverable state into a reusable state. Under the simple recovery model, every time you checkpoint, you will check to see if a log can be truncated . If there is a inactive VLF, checkpoint truncates the truncated part and pushes the minlsn backwards.

When the log reaches the end of the log file (LDF file), that is, the VLF8, it is cycled back to VLF1 to allow the space to be reused. So the log can be from VLF1 to VLF8 in physical order, but the logical order can be from VLF6 to VLF2 end:

As you can see, the log is not saved in the Simple recovery mode (when the transaction ends, the correlation is truncated). It is only used to guarantee transaction rollback and crash recovery. So the backup log is no longer a discussion, and the log can not be used to recover the database.

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