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

Introduction

Transaction logs in SQL Server are undoubtedly one of the most important parts of SQL Server. Because SQL server uses transaction logs to ensure durability and transaction rollback ). This also partially ensures the acid attribute of the transaction. When SQL server crashes, DBA can also recover data to the specified time point through the transaction log. When SQL server runs well, it is not so important to know more about the principles and concepts of transaction logs. However, when SQL server crashes, understanding the principles and concepts of transaction logs is especially important for quick and correct decisions to restore data.ArticleThe transaction logs of SQL Server will be discussed in terms of the concept and principle of transaction logs, and how SQL server uses logs to ensure persistent attributes.

 

Physical Structure of transaction logs

Transaction logs only record the transaction behavior of the corresponding database and the log files modified for the database. when you create a database, there will be a transaction log file with the default LDF extension along with the database file. of course, a database can also be equipped with multiple log files, but logically, they can be regarded as one.

SQL Server manages log files by dividing a logical LDF file into multiple logical virtual log files (vlfs) for ease of management. By analogy, the log file (LDF) is similar to a train, and each carriage is a virtual log file (vlfs ):

 

So why does SQL Server divide log files into multiple vlfs? This method makes the storage engine more effective in managing transaction logs, and the reuse of log space is more efficient. Using VLF as the minimum unit for database shrinking is more efficient than using LDF as the minimum unit.

The number and size of vlfs cannot be set through configuration, but are managed by SQL Server. When you create or alter a database, SQL Server determines the size and quantity of vlfs by the size of the LDF file. When log files increase, SQL Server will re-plan the number of vlfs.

Note: Based on this principle, it is not difficult to read books. If the log file increment is too small, too many vlfs will be generated, that is, log file fragments. Excessive Log File fragments will drag the performance of SQL Server.

When creating a database, SQL Server generates the following formula for the number of VLF Based on the log file (LDF:

LDF File Size	Number of VLF instances
1 m to 64 m	4
64 m to 1 GB	8
Larger than 1 GB	16

Here is an example:

Create a database and set the log size to 65 MB.

Through DBCC, we can see that there are eight vlfs:

Create a database again and specify the initial log size as 28 MB:

As you can see, the number of corresponding VLF changes to 4:

For the growth of log files, SQL server uses the same formula as when creating a database, that is, every growth for example, 2 m, 4 vlfs are increased each time according to the formula.

Create a testgrow database and specify the log file as 2 MB. At this time, there are four vlfs:

When we increase by 2 MB, the 2 Mb is based on the formula and four vlfs are allocated again:

At this time, we can see that the number of vlfs should be 4 + 4 = 8:

It can be seen that specifying the appropriate initial size and growth of log files is the most critical part to reduce log fragments.

Logical Structure of transaction logs

When any changes made to the database object are saved to the database, the corresponding logs are first recorded in the log file. This record is recorded at the end of the logic of the log file in sequence and assigned a globally unique log sequence number (LSN ), this serial number is in order. If the two serial numbers in the log are lsn2> lsn1, it indicates that lsn2 occurs after lsn1.

It can be seen that the log file is divided into multiple files in addition to disk space considerations. It does not support parallel access like data, so there is no performance improvement when dividing log files into multiple log files.

The lsn can be seen as the link between the log file and the recorded data. Each log not only has the lsn, but also the transaction log of the corresponding transaction:

A simple image example is as follows:

Many types of operations are recorded in the transaction log. These operations include:

• Start and end of each transaction.

• Each data modification (insert, update, or delete ). This includes changes made to any table, including system stored procedures or Data Definition Language (DDL) statements.

• Zones and pages are allocated or released each time.

• Create or delete a table or index.

 

How the lsn works in rollback or roll forward and in the backup and recovery process will be mentioned in subsequent articles

 

 

Summary

This article briefly introduces the structure of transaction logs from the logic and physical architecture of transaction logs, which is the basis for understanding how SQL server uses logs to ensure durability and data backup and recovery. The next article will introduce how SQL server uses log files during operations.