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

Introduction

The transaction log in SQL Server is undoubtedly one of the most important parts of SQL Server. Because SQL Server uses transaction logs to ensure persistence (durability) and transaction rollback (Rollback). This also partially ensures the acid properties of the transaction. When SQL Server crashes, DBAs can also recover data to a specified point in time through the transaction log. When SQL Server is working well, it is not so important to know the principles and concepts of some transaction logs. However, once SQL Server crashes, understanding the principles and concepts of the transaction log is especially important to quickly make the right decisions to recover the data. This series of articles will discuss SQL in terms of the concept of transaction logs, how SQL Server uses logs to ensure persistence properties, and so on. The transaction log for the server.

the physical organizational structure of the transaction log

The transaction log is simply a log file that records the transaction behavior on its corresponding database and modifies the database. When you create a new database, there is a default transaction log file with an LDF extension. Of course, a database can also be equipped with multiple log files, but logically they can be viewed as a .

In SQL Server management of log files, it is logical that an LDF file is divided into logical virtual log files (Vsan log file, abbreviated VLFS). for easy management. Using an analogy method, the log file (LDF) is like a train, and each compartment is a virtual log file (VLFs):

So why does SQL Server divide the log files into multiple VLFs? Because SQL Server makes the storage engine manage the transaction log in this way, it is more efficient to reuse the log space. Using VLF as the smallest unit of shrinking a database is undoubtedly more efficient than using an LDF file as the smallest unit.

The number and size of VLFs cannot be set by configuration, but is managed by SQL Server. When you create or alter a database, SQL Server determines the size and number of VLFs by the size of the LDF file. SQL server also re-plans the number of VLFs when the log file grows.

Note: According to this principle is not difficult to read, if the set log file increment is too small, it will produce too much vlfs, that is, log file fragmentation, excessive log file fragmentation will slow down SQL Server performance.

when SQL Server creates a database, the number of VLF is generated according to the size of the log file (LDF) as follows :

Size of the LDF file	Number of VLF
1M to 64M	4
64M to 1GB	8
Greater than 1GB	16

Let's look at an example:

Create a database that specifies a log size of 65M

With DBCC, we can see that there are 8 vlfs that correspond to each other:

Create the database again, specifying a log initial size of 28M:

As you can see, the corresponding, VLF number becomes 4:

With the growth of the log file, SQL Server uses the same formula that was used to create the database, that is, each time it grows for example 2M, it grows by 4 VLFs per formula.

We create a Testgrow database that specifies a log file of 2M, at which time there are 4 VLFs:

When we grow 2M, this 2M is then allocated 4 VLFs by formula:

At this point, you can see the number of VLFs should be 4+4=8:

As you can see, specifying the appropriate log file initial size and growth is the most critical part of reducing log fragmentation.

logical organizational framework for transaction logs

When any modifications made to the database object are saved to the database, the corresponding log is first logged to the log file. This record is logged to the logical end of the log file in chronological order and assigns a globally unique log sequence number (sequence number, referred to as LSN), which is exactly in order, if two serial numbers are LSN2>LSN1 in the log, The LSN2 is the LSN1 that occurs after the location.

as you can see, the log file is divided into multiple files in addition to disk space considerations. There is no parallel access like data, so the log files are divided into multiple, completely no performance improvements.

The LSN number can be thought of as a link between the log file and its record data. Each log has not only the LSN number, but also the transaction log of its corresponding transaction:

A simple picture example is as follows:

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

• The start and end of each transaction.

• Each data modification (INSERT, update, or delete). This includes changes made by system stored procedures or data definition language (DDL) statements to any table, including system tables.

• Each allocation or deallocation area and page.

• Creates or deletes a table or index.

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