Probe into the transaction log (I.)

In front of the actual problems encountered because of the actual operation of using transaction log backup to restore data, I think it is necessary to straighten out the knowledge of the transaction log, then use a few time to systematically summarize the basic principles of transaction logging and practical application.

What is a transaction log?

The transaction log is a file that is separate from the database file. It stores all changes made to the database and records all INSERT, UPDATE, delete, commit, fallback, and database schema changes. The transaction log is also known as a roll forward or redo log. The transaction log is an important component of backup and recovery and is required to replicate data using SQL Remote or [replication agent].

Talking about business has to come up with a concept that is the acid attribute, which consists of atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability) four characteristics. SQL Server uses transaction logs to ensure persistence (durability) and transaction rollback (Rollback). This also partially ensures the acid properties of the transaction.

Physical architecture of the transaction log

The transaction log stores the changes that were made to the database, allowing SQL Server to recover the database. In this way we need to store the corresponding information, familiar to the people who are familiar with MSSQL, that is, when creating the database, the. ldf file created, let's look at its physical structure.

In SQL Server, a log file is a virtual log file that divides logically an LDF file into multiple logical (Vsan log files, referred to as VLFs), which makes the storage engine manage the transaction log more efficiently. And reuse of log space is more efficient.

The transaction log is a wrapped file. For example, suppose you have a database that contains a physical log file that is divided into four virtual log files. When you create a database, the logical log file starts at the beginning of the physical log file. The new log record is added to the end of the logical log and then expands to the end of the physical log. Log truncation frees all virtual logs that appear before the minimum recovery log sequence number (MinLSN) is recorded. "MinLSN" is the log sequence number of the oldest log record that is required for a successful database-wide rollback.

SQL Server will determine the number of VLF according to the following rules:

log size	VLF
Size <= 1MB
1MB < Size <= 64MB	4
64MB & Lt Size <= 1GB	8 x
Size > 1GB	16

transaction log Logical Architecture

The SQL Server transaction log runs logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log and uses a higher LSN than the previously recorded LSN. Logging is stored as a serial sequence when it is created. Each log record contains the ID of the transaction to which it belongs. For each transaction, all the log records associated with the transaction are linked in one chain by using a backward pointer that improves the rollback speed of the transaction.

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.

Regardless of how many physical files are defined for the transaction log, SQL Server always treats the log as a continuous stream (contiguous stream).

The recovery steps for an operation depend on the type of logging:

• Logging logical operations

  • To roll forward the logical operation, perform the operation again.

  • To roll back a logical operation, perform the opposite logical operation.

• Record before and after image

  • To roll forward the operation, apply the post-image.

  • To roll back the action, apply the pre-image.

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.

Summarize:

This chapter provides a brief description of the transaction log composition from the logical and physical architecture of the transaction log. It is important to note that if SQL Server sets the simple recovery model, the database periodically truncates the log, or if a full database backup has never been made, it is considered that there is no maintenance log backup. The next chapter describes how SQL Server will use the log files in operations.

Probe into the transaction log (I.)