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
000000000000003
C:
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
000000000
BE4FC70:
42006500
69006a00 69006e00
67002000
48006100
B.e.i.j.i.n.g. .H.a.
000000000
BE4FC84:
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 的数量。日志总是以整个 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 发表自 博客园 个人技术博客，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载或抄袭行为均为耍流氓。
