SQL Serve 日志体系结构

SQL Server 事务日志记录着 undo 及 redo 日志，为了保证数据库在崩溃后恢复，或者在正常数据库操作期间进行回滚，保证数据库事务完整性和持久化。如果没有事务日志记录，数据库在事务上将不一致，并且在数据库崩溃后可能导致结构上损坏。

SQL Server 日志记录最初在内存中处理，可能在事务提交之前写入磁盘，单必须在事务完成提交之前写入磁盘，否则事务不会持久化（延迟事务特性例外）。

事务日志的内部结果是什么样子呢？

结构层次

事务日志在内部使用三层结构进行组织，如下图所示。

事务日志包含虚拟日志文件，虚拟日志文件包含存储实际日志记录的日志块。

事务日志分为多个虚拟日志文件（virtual log files），通常称为VLF。这样做是为了让 SQL Server 中的日志管理器更轻松地管理事务日志中的操作。你无法指定 SQL Server 在首次创建数据库或日志文件自动增长时创建了多少 VLF，但你可以影响它。创建多少个 VLF 的算法如下：

• 日志文件大小小于 64MB：创建 4 个 VLF，每个大小约为 16 MB

• 日志文件大小从 64MB 至 1GB：创建 8 个 VLF，每个大约是总大小的 1/8

• 日志文件大小大于 1GB：创建 16 个 VLF，每个大约是总大小的 1/16

在 SQL Server 2014 之前，当日志文件自动增长时，添加到日志文件末尾的新 VLF 数量由上述算法根据自动增长大小确定。但是，使用这种算法，如果自动增长的大小很小，并且日志文件经历了许多自动增长，它可能会导致大量的小 VLF（称为VLF 碎片），这可能是一个很大的性能问题。

由于这个问题，在 SQL Server 2014 中，日志文件的自动增长算法发生了变化。如果自动增长的大小小于总日志文件大小的 1/8，则只创建一个新的 VLF，否则使用旧算法。这大大减少了由日志文件频繁自动增长的 VLF 数量。

每个 VLF 都有一个唯一标识它的序列号，并用于各种地方，你可能认为全新数据库的序列号将从 1 开始，但事实并非如此。

在 SQL Server 2019 实例上，我创建了一个新数据库，但未指定任何文件大小，然后使用以下代码检查 VLF：

CREATE DATABASE NewDB;GO
--MSSQL 2016 SP2开始可用SELECT    [file_id],     [vlf_begin_offset],    [vlf_size_mb],    [vlf_sequence_number]FROM sys.dm_db_log_info (DB_ID (N'NewDB'));GO
--MSSQL 任意版本可用DBCC LOGINFO('NewDB');GO

请注意，sys.dm_db_log_info 是在 SQL Server 2016 SP2 中添加的。你也可以使用 DBCC LOGINFO 命令查看，FSeqNo为 VLF 序列号。

请注意，第一个 VLF 从日志文件的偏移量 8192 字节开始。这是因为包括事务日志在内的所有数据库文件都有一个文件头页，该页占用了前 8KB，并存储了有关该文件的各种元数据。

那么为什么 SQL Server 选择 36 而不是 1 作为第一个 VLF 序列号呢？这是因为数据库的创建都是以 model 作为参考，model 中的最大 VLF 序列号加上1，即为新数据库的 VLF 序列号。该算法从 SQL Server 7.0 开始就已使用。

为了证明这一点，我运行了以下代码：

SELECT MAX ([vlf_sequence_number]) AS [Max_VLF_SeqNo]FROM  sys.dm_db_log_info (DB_ID (N'model'));GODBCC LOGINFO('model');GO

现在只要知道每个 VLF 都有一个序列号就足够了，每个 VLF 序列号增量为1。

日志块

每个 VLF 包含一个小的元数据标头，其余空间由日志块填充。每个日志块从 512 字节开始，以 512 字节为增量增长到最大 60KB，此时必须将其写入磁盘。如果发生以下情况之一，日志块可能会在达到其最大大小之前写入磁盘：

• 事务提交，并且延迟持久性未作用于该事务，因此必须将日志块写入磁盘以使事务持久；

• 使用延迟持久化，后台“flush the current log block to disk”每1ms定时触发；

• 检查点或惰性写入器正在将数据文件页面写入磁盘，并且当前日志块中有日志记录影响要写入的页面（预写日志）；

你可以将日志块看作是大小可变的页面，它按照事务更改数据库创建的顺序存储日志记录。每个事务没有专门的日志块，多个并发事务的日志记录可以混合在一个日志块中。你可能认为，当去寻找单个事务的所有日志记录时会带来困难，但事实并非如此。此外，当一个日志块被写入磁盘时，它完全有可能包含来自未提交事务的日志记录。

日志序列号（LSN）

日志块在 VLF 中有一个 ID，从 1 开始，对于 VLF 中的每个新日志块增量为 1。日志记录在日志块中也有一个 ID，从 1 开始，对于日志块中的每个新日志记录增加 1。因此，事务日志的结构层次结构中的所有三个元素都有一个 ID，它们合并为一个称为日志序列号的三方标识符中，通常简称为LSN。

一条 LSN 定义为 <VLF sequence number>:<log block ID>:<log record ID>（4 字节：4 字节：2 字节），唯一标识一条日志记录。这是一个不断增加的标识符，因为 VLF 序列号永远增加。

你也可以使用 fn_dblog 查看当前数据库的事务日志：

--sys.fn_dblog(Start LSN nvarchar(25), End LSN nvarchar(25))SELECT [Current LSN] FROM sys.fn_dblog(NULL, NULL)