原文:人人都是 DBA（VI）SQL Server 事务日志
SQL Server 的数据库引擎通过事务服务（Transaction Services）提供事务的 ACID 属性支持。ACID 属性包括：
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
事务日志（Transaction Log）
事务日志（Transaction Log）存储的是对数据库所做的更改信息，让 SQL Server 有机会恢复数据库。而恢复（Recovery）的过程就是使数据文件与日志保持一致的过程。任何在日志中指示已经提交的数据更改必须出现在数据文件中，任何未标记为提交的更改不能出现在数据文件中。
预写日志（Write-ahead Logging）功能确保在真正发生变化的数据页写入磁盘前，始终先在磁盘中写入日志记录，使得任务回滚成为可能。写入事务日志（Transaction Log）是同步的，即 SQL Server 必须等它完成。但写入数据页可以是异步的，所以可以在缓存中组织需要写入的数据页进行批量写入，以提高写入性能。
事务日志用于保证 SQL Server 在语句或系统出现故障时的可恢复性，并允许将备份的日志应用到数据库上。但事务日志并没有提供很好的可读性，实际上读取事务日志通常也不会获取到太多有用信息。更推荐的跟踪记录机制是使用 SQL Server Profiler 等工具，以筛选和捕获有用的信息。
比如，我们使用下面的 SQL 来创建一张简单的 Table，来尝试观察事务日志的变化。
CREATE TABLE [dbo].[Customer](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Address] [nvarchar](max) NULL,
[Phone] [nvarchar](256) NULL
) ON [PRIMARY]
插入一条记录。
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Phone])
VALUES
('Dennis Gao'
,'Beijing Haidian'
,'88888888')
使用 DBCC LOG 命名可以先观察产生的序列。
DBCC LOG([TEST])
使用系统提供的函数 sys.fn_dblog 来查看当前的事务日志记录，可以列出很多详细信息，这里只显示了几个常用的列。
SELECT [Current LSN]
,[Operation]
,[Context]
,[Transaction ID]
,[Log Record Length]
,[Previous LSN]
,[AllocUnitId]
,[AllocUnitName]
,[Page ID]
,[Slot ID]
,[Xact ID]
FROM sys.fn_dblog(NULL, NULL)
事务日志总是连续的并且是顺序的，按照 LSN（Log Sequence Number）的顺序排列。从查询的尾部可以查看 AllocUnitName 操作的数据表名称。
对应的 Operation 是 LOP_INSERT_ROWS，Context 是 LCX_HEAP，也就是插入数据到堆表。同时发现 Page ID 是 0001:00000078，也就是十进制的 120 号页面。
可以使用 DBCC PAGE 命令查看 Page 页信息。
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
DBCC TRACEON(3604, -1) GO DBCC PAGE([TEST], 1, 120, 3) GO
PAGE: (1:120) BUFFER: BUF @0x000000027D15AC80 bpage = 0x000000026B6BA000 bhash = 0x0000000000000000 bpageno = (1:120) bdbid = 7 breferences = 0 bcputicks = 0 bsampleCount = 0 bUse1 = 8595 bstat = 0x10b blog = 0x1215accc bnext = 0x0000000000000000 PAGE HEADER: Page @0x000000026B6BA000 m_pageId = (1:120) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000 m_objId (AllocUnitId.idObj) = 87 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043629568 Metadata: PartitionId = 72057594039107584 Metadata: IndexId = 0 Metadata: ObjectId = 565577053 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 12 m_slotCnt = 1 m_freeCnt = 8005 m_freeData = 185 m_reservedCnt = 0 m_lsn = (33:460:24) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 89 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 89 Memory Dump @0x000000000BE5A060 0000000000000000: 30000c00 01000000 00000000 04000003 002b0049 0................+.I 0000000000000014: 00590044 0065006e 006e0069 00730020 00470061 .Y.D.e.n.n.i.s. .G.a 0000000000000028: 006f0042 00650069 006a0069 006e0067 00200048 .o.B.e.i.j.i.n.g. .H 000000000000003C: 00610069 00640069 0061006e 00380038 00380038 .a.i.d.i.a.n.8.8.8.8 0000000000000050: 00380038 00380038 00 .8.8.8.8. Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8 Id = 1 Slot 0 Column 2 Offset 0x17 Length 20 Length (physical) 20 Name = Dennis Gao Address = [BLOB Inline Data] Slot 0 Column 3 Offset 0x2b Length 30 Length (physical) 30 000000000BE4FC70: 42006500 69006a00 69006e00 67002000 48006100 B.e.i.j.i.n.g. .H.a. 000000000BE4FC84: 69006400 69006100 6e00 i.d.i.a.n. Slot 0 Column 4 Offset 0x49 Length 16 Length (physical) 16 Phone = 88888888
可以看出上面的 SQL 语句 Insert 了数据 Id = 1, Name = Dennis Gao, Phone = 88888888。
虚拟日志文件（VLF：Virtual Log File）
不管为事务日志定义多少个物理文件，SQL Server 总是把日志当成连续流（Contiguous Stream）来对待。当 DBCC SHRINKDATABASE 命令确认日志可以缩小多少时，它不是单独考虑每个日志文件，而是根据整个日志来确定可压缩大小。
SQL Server 数据库的事务日志是通过虚拟日志文件（VLF：Virtual Log File）来管理的，VLF 的大小由 SQL Server 根据日志的总大小和日志增量大小来决定，不能通过配置指定。如果 VLF 数量变多会导致数据库性能下降，所以需要指定合理的日志文件初始大小和增长步长，防止过多的 VLF 的产生。
SQL Server 会根据如下规则来判断 VLF 的数量：
| 日志大小 |
VLF 数量 |
|
Size <= 1MB |
将日志文件大小除以最小 VLF 大小（31KB*8KB）确定个数 |
|
1MB < Size <= 64MB |
4 个 |
|
64MB < Size <= 1GB |
8 个 |
|
Size > 1GB |
16 个 |
当日志持续增长时，会使用相同的方式确定新添加的 VLF 的数量。日志总是以整个 VLF 为单位增长，而且缩小也只能到 VLF 的边界为止。
VLF 可以处于以下 4 种状态之一。
- Active：日志的活动部分，从未提交事务的最小 LSN 开始，结束于最后一个写入的 LSN。
- Recoverable：在最早的活动事务之前的那部分日志。
- Reusable：如果日志已经被备份，则不需要最早活动事务之前的 VLF，可重用这些空间。日志截断或备份会将 Recoverable VLF 转换成 Reusable VLF。
- Unused：未使用的部分。
可以使用下面的 SQL 查询 VLF 的数量。
CREATE TABLE #VLFInfo (
RecoveryUnitID INT
,FileID INT
,FileSize BIGINT
,StartOffset BIGINT
,FSeqNo BIGINT
,[Status] BIGINT
,Parity BIGINT
,CreateLSN NUMERIC(38)
);
CREATE TABLE #VLFCountResults (
DatabaseName SYSNAME
,VLFCount INT
);
EXEC sp_MSforeachdb N'Use [?];
INSERT INTO #VLFInfo
EXEC sp_executesql N''DBCC LOGINFO([?])'';
INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;
TRUNCATE TABLE #VLFInfo;'
SELECT DatabaseName
,VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;
可以使用 DBCC LOGINFO 命令进一步观察 VLF 的相关属相。
DBCC LOGINFO
SQL Server 可以配置多个物理日志文件当做一个序列流来对待。如果管理良好，定期备份或截断日志，可能永远都不会使用除第一个文件之外的其他日志文件。当需要新的 VLF 时，多个物理文件中都没有可用 VLF，则会以循环的方式把新的 VLF 添加到每个物理日志文件中。
自动截断模式（Auto Truncate Model）
如果 SQL Server 设置了如下情况，则认为没有维护日志备份：
- 设置 SIMPLE 恢复模型，数据库会定期截断日志。
- 从未进行过完全数据库备份。
以上任何一种情况下，SQL Server 会处于自动截断模式（Auto Truncate Model）中，当数据库事务日志满时就会进行截断。这里的 "满" 指的是日志记录的数量比在系统启动过程中、在合理的时间内能够重做的数量多。
判断数据库是否在自动截断模式的最简单的方法是查询 sys.database_recovery_status 目录视图，如果 last_log_backup_lsn 列为空，则数据库就是处于自动截断模式。
SELECT * FROM sys.database_recovery_status;
可以通过 DBCC SQLPERF 命名来查看日志文件大小。
DBCC SQLPERF('logspace')
当然，也可以通过系统提供的目录视图来查看。
SELECT instance_name AS [Database]
,cntr_value AS [LogFull(%)]
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Percent Log Used%'
AND instance_name NOT IN (
'_Total'
,'mssqlsystemresource'
)
AND cntr_value > 0
ORDER BY [LogFull(%)] DESC;
可以使用 DBCC 命令来压缩事务日志文件，下面是 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 的语法。
DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
《人人都是 DBA》系列文章索引：
本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园个人技术博客，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载或抄袭行为均为耍流氓。