Transaction logs in SQL Server (iii) -- Role of logs in simple recovery mode

This articleArticleIs the third article in the series. The addresses of the first two 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

 

Introduction

In simple recovery mode, log files only ensure the acid attribute of SQL Server transactions. Does not assume the specific data recovery role. Just as the literal meaning of the word "simple", data backup and recovery only rely on manual backup and recovery. before starting the article, you should first understand several different backup types provided by SQL Server.

 

Several backup types provided by SQL Server

SQL Server provides the following types of backup ):

1. Full backup: directly copies all the regions (extent) of the backed up data. Note the following two points:

• The full backup does not back up all the parts as it is named "complete". Instead, it only backs up the database itself, instead of the logs (although only a small number of logs are backed up for synchronization)
• The database is available during full backup. The complete backup records the minlsn at the start of the backup, and the lsn at the end of the backup, backs up the logs in this interval, and uses the recovered database when restoring (modified here, thank Mo Jun for pointing out)

 

2. Differential backup: only the modified part after the last full backup is backed up. The backup unit is extent ). This means that even if only one page is changed in a region, it will be reflected in differential backup. differential backup relies on a bitmap for maintenance. A bit corresponds to a zone. After the last full backup, the modified zone is set to 1, in bitmap, the region set to 1 is backed up by differential backup. After the next full backup, all bits in bitmap will be reset to 0.

 

3. Log backup: only records after the last full backup or log backup are backed up. In simple mode, log backup is meaningless (SQL Server cannot back up logs in simple recovery mode). The following describes why log backup is meaningless in simple recovery mode.

 

Simple recovery mode)

In simple recovery mode, logs are only used to ensure acid of SQL Server transactions. Data restoration is not available.

For example, we have a backup plan as follows:

We perform a full backup at every Monday and differential backup at on Wednesday and on Friday. In simple recovery mode, if the database crashes on Saturday. Our recovery plan is to use the differential backup at on Friday for recovery only after the complete backup is restored at on Monday. when the server crashes after on Friday, all data will be lost.

As described in the word "simple", logs are not managed in the simple recovery mode. Backup and recovery depend entirely on our own complete and differential backup.

The recovery mode is a database-level parameter that can be configured in SSMs or through SQL statements:

 

Space usage of logs in simple recovery mode

As mentioned in the first article in this series of articles, log files are divided into multiple VLF for Management. The records are logically linear and each record is ordered, the unique lsn.

In simple recovery mode, to ensure transaction persistence, data that may be rolled back will be written into the log. These logs need to be temporarily stored in logs to ensure that transactions can be rolled back smoothly under specific conditions. This involves the concept of least-restored lsn (minimum recovery lsn (minlsn ))

Minlsn is the smallest LSN in the log for transactions that have not been completed, and minlsn is the minimum value of one of the following three:

• Start lsn of checkpoint

• The minimum lsn of the uncompleted transaction in the log

• The lsn of the earliest replication transaction start point that has not been passed to the distribution database.

 

Is a log segment:

(Image taken from msdn)

We can see that the latest lsn is 148,147, which is the checkpoint. Before this checkpoint, transaction 1 has been completed, and transaction 2 has not been completed. Therefore, the corresponding minlsn should be the beginning of transaction 2, 142.

From the minlsn to the end of the log logic, it is called the active log ).

The relationship between active logs distributed on physical VLF can be expressed as follows:

 

Therefore, the VLF State is derived from the lsn State contained on it. It can be divided into two categories: Active VLF and non-active VLF.

However, more subdivided VLF states can be divided into the following four categories:

1. Active)-When any lsn stored on VLF is active, VLF is active, even if a m VLF contains only one lsn, such as vlf3
2. Recoverable)-VLF is not active. VLF does not contain the active lsn, but it has not been truncated (truncated)
3. Reusable)-VLF is inactive. VLF does not contain the active lsn. It has been truncated and can be reused.
4. Unused (unused)-VLF is not active and has not been used.

Concepts include:

The so-called truncated only converts a recoverable VLF to a reusable state. In simple recovery mode, each checkpoint checks whether logs can be truncated. if there is an inactive VLF, the checkpoint will be truncated and the minlsn will be pushed back.

When the log reaches the end of the log file (LDF file), when vlf8 is the same, it will be recycled to the beginning of vlf1, so that the space can be reused. therefore, although the log can be from vlf1 to vlf8 in physical order, the logical order can be from vlf6 to vlf2:

Therefore, logs are not saved in simple recovery mode (related logs are truncated after the transaction ends ). It is only used to ensure transaction rollback and crash recovery. Therefore, backup logs cannot be discussed, and logs cannot be used to restore the database.

 

Summary

This article introduces the log principles in simple recovery mode and briefly introduces some basic information for backing up or restoring data. In fact, apart from development or testing environments. There are not many scenarios that use the simple recovery mode, because in real life, there are almost no scenarios that allow data loss for several hours in the production environment. the next article will describe the role of logs in the full recovery mode.