SQL Server Transaction Log

The SQL Server transaction log file is an important part of the database file, and the transaction log is used primarily to store the database's modification records. In order to achieve higher write efficiency and performance while maintaining the acid characteristics, the data is written to the transaction log when the update is written, because the transaction log is ligatures, so the write transaction is faster. Simply put, when sequential writes, the disk's head will remain in a certain area of continuous write, while the data written to the data file, there is randomness, the disk's head movement consumes more time than the data written to the log file.

SQL Server management of transaction log files is a logical partitioning of log files into several files (VLFS) for easy administration.

Create a 1M log file

Execute DBCC LOGINFO

Here you can see that the transaction log is partitioned into 4 virtual log files, so what is the size of the log file and the number of VLFs allocated when SQL Server creates the database? If the log file increases, how does the VLFs change?

The number of VLFs is managed by SQL Server, and when the Create or alter LDF file is created, SQL Server allocates VLFs files based on the initial value or the incremental size. LDF file each increment and the number of VLFs per allocation is as follows

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

All modifications to the database are recorded in the transaction log before they are written to the data file. The log records are recorded sequentially to the logical end of the log file and assigned a globally unique log sequence number (log Sequence number), which is assigned in order.

In the log, there is the concept of an active log, which can be understood as a transaction log that is not written to the data file.

When the database is checkpoint, the committed log data is flushed to the data file, and the activity log interval is moved to the uncommitted log sequence number.

In the simple log mode, after checkpoint, the contents of the inactive log will be emptied and the log space after emptying can be flushed. This is also why log files are always very small in the simple log mode.

In full or bulk log mode, log space can be reused only after the log is backed up.

Many types of operations are logged in the transaction log, including the following:

Start and end of each transaction

Every data modification

Each allocation or release area or page

Create a delete table or index

SQL Server Transaction Log