人人都是 DBA（VI）SQL Server 事务日志

浏览数：27 / 时间：2015年06月12日

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 ]