本文详细介绍了SQL Server事务日志的体系结构，包括文件拓扑、基本概述、LSN和MinLSN的原理以及日志截断过程。事务日志是数据库ACID合规性的关键，用于保证数据一致性并在系统恢复、灾难恢复时发挥作用。日志记录按预写日志机制写入，每个记录有唯一的LSN。MinLSN标记最老的活跃日志记录，用于回滚。日志截断则删除不活动记录，释放空间。了解这些对于优化数据库性能至关重要。
This article will cover SQL Server transaction log architecture including file topography, basic overview, review of LSN and MinLSN, and log truncation
本文将介绍SQL Server事务日志体系结构，包括文件拓扑，基本概述，LSN和MinLSN的回顾以及日志截断
地形 (Topography)
A SQL Server database consists mainly of three files, the primary data file (.mdf), the secondary data file (.ndf) and the transaction log file (.ldf). As the name indicates, the data files are used to store the tables data within the data page. The SQL Server transaction log file is used to write a record for each data modification operation, including an image of the data before and after the modification process.
SQL Server数据库主要由三个文件组成，主要数据文件（.mdf），次要数据文件（.ndf）和事务日志文件（.ldf）。 顾名思义，数据文件用于在数据页面内存储表数据。 SQL Server事务日志文件用于为每个数据修改操作写入一条记录，包括修改过程之前和之后的数据映像。
总览 (Overview)
In the previous article of this series SQL Server Transaction Overview, we described the concept of the SQL Server transaction. In this article we’ll review the architecture of the transaction log file.
在本系列“ SQL Server事务概述”的上一篇文章中，我们描述了SQL Server事务的概念。 在本文中，我们将回顾事务日志文件的体系结构。
The transaction log is a critical component of a SQL Server database for ACID (Atomicity, Consistency, Isolation and Durability) compliance. When the SQL Server service is restarted, the database enters into the Recovery state, in which the SQL Server Database Engine reads the SQL Server transaction log file to make sure that the database is in a consistent state. It does this by writing the committed transactions data to the data file in a roll-forward process, and undoing all uncommitted transactions in a roll-back process. In addition, the transaction log file is used to restore the database to a specific point of time, in case of a disaster or system failure. It can be also used to return the database to the previous state when a ROLLBACK command is executed after a transaction.
事务日志是SQL Server数据库中ACID（原子性，一致性，隔离性和耐久性）合规性的关键组成部分。 重新启动SQL Server服务后，数据库进入“恢复”状态，在该状态下，SQL Server数据库引擎读取SQL Server事务日志文件以确保数据库处于一致状态。 它通过在回滚过程中将已提交的事务数据写入数据文件，并在回滚过程中撤消所有未提交的事务来做到这一点。 另外，在灾难或系统故障的情况下，事务日志文件用于将数据库还原到特定的时间点。 当在事务之后执行ROLLBACK命令时，它还可用于使数据库返回到先前的状态。
The functionality of the SQL transaction log is achieved by writing a log record to the transaction log file before writing the data pages to the physical data file, in a process called Write-ahead Logging. The SQL Server Database Engine writes a log record for each single operation, such as writing a log at the beginning and at the end of each SQL transaction, after each data modification process, when creating or dropping a database table or index, and after each page allocation or deallocation process. These logs will be written to and read from the transaction log file sequentially.
SQL事务日志的功能是通过在称为预写日志记录的过程中将数据页写入物理数据文件之前，将日志记录写入事务日志文件来实现的。 SQL Server数据库引擎为每个单个操作写一个日志记录，例如在每个数据修改过程之后，在创建或删除数据库表或索引时以及在每个SQL事务的开头和结尾处写一个日志。页面分配或释放过程。 这些日志将依次写入事务日志文件和从中读取。
SQL Server allows us to create multiple SQL Server transaction log files on each database. Each transaction log file is divided internally into multiple Virtual Log Files, also known as VLFs. The size and number of the VLFs on each transaction log file is dynamic, where the SQL Server Database Engine starts with the least possible number of VLFs on the transaction log file and extend it, based on the defined increment, when the file runs out of free space.
SQL Server允许我们在每个数据库上创建多个SQL Server事务日志文件。 每个事务日志文件在内部均分为多个虚拟日志文件 ，也称为VLF。 每个事务日志文件上的VLF的大小和数量都是动态的，其中SQL Server数据库引擎从事务日志文件上的VLF数量最少开始，然后在文件用完时根据定义的增量对其进行扩展。可用空间。
The number and the size of the VLFs affect the performance of the database startup, backup and restore operations. To override these performance issues, we should tune both the SQL transaction log file initial size and auto-growth increment properly. We will cover the VLFs subject completely in the next article. It is not always recommended to have multiple transaction log files in your database, as it may impact the performance of your database, due to writing the data sequentially and not in parallel. You can create another transaction log file as a workaround, in case of running the current hosting disk drive out of free space.
VLF的数量和大小会影响数据库启动，备份和还原操作的性能。 要解决这些性能问题，我们应该适当地调整SQL事务日志文件的初始大小和自动增长的增量。 在下一篇文章中，我们将完全涵盖VLF主题。 不总是建议在数据库中有多个事务日志文件，因为由于顺序而不是并行地写入数据，这可能会影响数据库的性能。 如果当前主机磁盘驱动器的可用空间不足，则可以创建另一个事务日志文件作为解决方法。
交易日志LSN (Transaction log LSN)
Each Transaction log record that is written to the SQL Server transaction log file can be identified by its Log Sequence Number (LSN). When the database is created, the Database Engine starts writing at the beginning of the logical transaction log file, which is the beginning of the actual physical transaction log file, and mark the end of the written log as the end of the logical log file. When a new transaction is performed, the log records will be written serially to the end of the logical transaction log file, with an LSN value higher than the LSN value of the previous log record.
写入SQL Server事务日志文件的每个事务日志记录都可以通过其日志序列号 （LSN）进行标识。 创建数据库后，数据库引擎将从逻辑事务日志文件的开始（即实际物理事务日志文件的开始）开始写入，并将写入的日志的结尾标记为逻辑日志文件的结尾。 当执行新事务时，日志记录将被串行写入逻辑事务日志文件的末尾，其LSN值高于前一个日志记录的LSN值。
The serially inserted log records contain other useful information, such as the ID of the transaction that this record belongs to. In this case, all log records associated with a specific transaction will be grouped and linked in a chain based on the transaction ID, that speed the rollback process of that transaction. For example, querying the sys.fn_dblog system DMO using the following script shows us number of log records, with different LSNs, that belong to the same transaction, and linked together using the Transaction ID value, as shown clearly below:
串行插入的日志记录包含其他有用的信息，例如该记录所属的事务的ID。 在这种情况下，与特定事务关联的所有日志记录将基于事务ID分组并链接在链中，从而加快了该事务的回滚过程。 例如，使用以下脚本查询sys.fn_dblog系统DMO将向我们显示属于同一事务并使用“事务ID”值链接在一起的具有不同LSN的日志记录的数量，如下所示：
SELECT [Current LSN],[Operation] ,[Transaction ID],[Previous LSN] ,[AllocUnitName],[Previous Page LSN],
[Page ID],[XACT ID],[Begin Time],[End Time]
FROM sys.fn_dblog (NULL, NULL)
You can imagine the SQL Server transaction log file as a circular tape. When the end of the logical log reaches the end of the actual physical log, the Database Engine will write the new log by wrapping it around the beginning of the actual log file, in a circular way, or to the next transaction log file, if the database consists of multiple transaction log file, as shown below:
您可以将SQL Server事务日志文件想象为环形磁带。 当逻辑日志的末尾到达实际物理日志的末尾时，数据库引擎将通过将新日志以循环的方式环绕在实际日志文件的开头周围，或将其写入下一个事务日志文件中来写入新日志该数据库由多个事务日志文件组成，如下所示：
If the end of the logical log reaches the start of the logical log, due to one of the following reasons, the SQL Server Database Engine will return error number 9002, as there is no room available for the new transaction log record to be written in the SQL Server transaction log file:
如果由于以下原因之一，逻辑日志的结尾到达逻辑日志的开头，则SQL Server数据库引擎将返回错误号9002 ，因为没有空间可用于写入新的事务日志记录SQL Server事务日志文件：
- No proper truncate process is performed. 没有执行适当的截断过程。
- Auto-growth option is not enabled. 未启用自动增长选项。
- Auto-growth enabled but the disk drive is running out of free space. 启用了自动增长，但是磁盘驱动器的可用空间已用完。
MinLSN (MinLSN)
The Minimum transaction log Sequence Number, also known as MinLSN, is a special type of LSN, that shows the LSN of the oldest active log record that is required to perform a successful database rollback process. The portion of the SQL Server transaction log file between the MinLSN and the end of the logical log that is required for the full database recovery, is called the Active Log, as shown below:
最小事务日志序列号，也称为MinLSN， 是LSN的一种特殊类型 显示执行成功的数据库回滚过程所需的最旧的活动日志记录的LSN。 MinLSN和完整数据库恢复所需的逻辑日志末尾之间SQL Server事务日志文件部分称为活动日志 ，如下所示：
截断 (Truncation)
Log truncation process deletes all inactive VLFs from the SQL Server transaction log file. No part of the active log can ever be truncated. VLF is the smallest unit of truncation in the transaction log file. If there is one active log record within an VLF, the overall VLF will be considered as part of the active log.
日志截断过程将从SQL Server事务日志文件中删除所有不活动的VLF。 活动日志的任何部分都不能被截断。 VLF是事务日志文件中的最小截断单位。 如果一个VLF中有一个活动日志记录，则整个VLF将被视为活动日志的一部分。
To be able to truncate the SQL Server transaction log:
为了能够截断SQL Server事务日志：
- The transaction should be committed 交易应该被提交
- The transaction log is not pending any backup or high availability feature 事务日志未挂起任何备份或高可用性功能
- A Checkpoint operator should be triggered to mark the inactive portion of the transaction log as reusable 应该触发一个Checkpoint运算符，以将事务日志的非活动部分标记为可重用
When a data insertion or modification is performed on your database, the Database Engine keeps the performed change in the buffer pool memory, rather than applying it directly to the database files. In this way, it will perform less frequent I/O operations. The data pages that are stored in the buffer pool memory but not reflected yet to the database files are known as Dirty Pages. The process used by the Database Engine to reflect the dirty pages to the database files periodically is called Checkpoints.
在数据库上执行数据插入或修改时，数据库引擎会将执行的更改保留在缓冲池内存中，而不是直接将其应用于数据库文件。 这样，它将执行较少的I / O操作。 存储在缓冲池内存中但尚未反映到数据库文件的数据页称为“ 脏页” 。 数据库引擎用来定期将脏页反映到数据库文件的过程称为Checkpoints 。
When a log truncation process is performed, the Database Engine will free all the inactive log records, starting from the beginning of the logical log toward the MinLSN, for reuse by the actual physical log. For example, the below SQL Server transaction log file contains:
当执行日志截断过程时，数据库引擎将从逻辑日志的开始向MinLSN释放所有不活动的日志记录，以供实际的物理日志重用。 例如，下面SQL Server事务日志文件包含：
- Physical log with 5 VLFs 具有5个VLF的物理日志
- Logical log, which is the used part of the physical log, occupies the first four VLFs 逻辑日志（它是物理日志的使用部分）占据了前四个VLF
- The first two VLFs contains inactive logs that cannot be used for now 前两个VLF包含暂时不能使用的非活动日志
- The second two VLFs (VLF 3 and VLF 4) contain active log records that cannot be truncated 后两个VLF（VLF 3和VLF 4）包含无法截断的活动日志记录
After performing a truncate process on the previous SQL Server transaction log file, you will see that:
在上一个SQL Server事务日志文件上执行截断过程后，您将看到：
- The first two VLFs, that contain inactive log records, are truncated 包含无效日志记录的前两个VLF被截断
- VLF1 and VLF2 are available now for reuse again VLF1和VLF2现在可以再次使用
- VLF1 and VLF2 are no longer part from the logical log VLF1和VLF2不再是逻辑日志的一部分
- No change performed on VLF3 and VLF4 that are contain active log records 没有对包含活动日志记录的VLF3和VLF4执行任何更改
For now, we are familiar with the internal structure of the SQL transaction log file, its importance and how it works. In the next articles of this series, we will describe deeply the different aspects of the SQL Server transaction log. Stay tuned!
现在，我们已经熟悉了SQL事务日志文件的内部结构，它的重要性及其工作方式。 在本系列的下一篇文章中，我们将深入描述SQL Server事务日志的不同方面。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-transaction-log-architecture/
1222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
