SQL Server 的 數(shù)據(jù)庫(kù)引擎 通過 事務(wù)服務(wù)（Transaction Services） 提供事務(wù)的 ACID 屬性支持。ACID 屬性包括：
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔離性（Isolation）
- 持久性（Durability）
事務(wù)日志（Transaction Log）
事務(wù)日志（Transaction Log） 存儲(chǔ)的是對(duì)數(shù)據(jù)庫(kù)所做的更改信息，讓 SQL Server 有機(jī)會(huì)恢復(fù)數(shù)據(jù)庫(kù)。而 恢復(fù)（Recovery） 的過程就是使數(shù)據(jù)文件與日志保持一致的過程。任何在日志中指示已經(jīng)提交的數(shù)據(jù)更改必須出現(xiàn)在數(shù)據(jù)文件中，任何未標(biāo)記為提交的更改不能出現(xiàn)在數(shù)據(jù)文件中。
預(yù)寫日志（Write-ahead Logging） 功能確保在真正發(fā)生變化的數(shù)據(jù)頁(yè)寫入磁盤前，始終先在磁盤中寫入日志記錄，使得任務(wù)回滾成為可能。寫入 事務(wù)日志（Transaction Log） 是同步的，即 SQL Server 必須等它完成。但寫入數(shù)據(jù)頁(yè)可以是異步的，所以可以在緩存中組織需要寫入的數(shù)據(jù)頁(yè)進(jìn)行批量寫入，以提高寫入性能。
事務(wù)日志用于保證 SQL Server 在語(yǔ)句或系統(tǒng)出現(xiàn)故障時(shí)的可恢復(fù)性，并允許將備份的日志應(yīng)用到數(shù)據(jù)庫(kù)上。但事務(wù)日志并沒有提供很好的可讀性，實(shí)際上讀取事務(wù)日志通常也不會(huì)獲取到太多有用信息。更推薦的跟蹤記錄機(jī)制是使用 SQL Server Profiler 等工具，以篩選和捕獲有用的信息。
比如，我們使用下面的 SQL 來創(chuàng)建一張簡(jiǎn)單的 Table，來嘗試觀察事務(wù)日志的變化。
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 命名可以先觀察產(chǎn)生的序列。
DBCC
LOG
(
[
TEST
]
)
使用系統(tǒng)提供的函數(shù) sys.fn_dblog 來查看當(dāng)前的事務(wù)日志記錄，可以列出很多詳細(xì)信息，這里只顯示了幾個(gè)常用的列。
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
)
事務(wù)日志總是連續(xù)的并且是順序的，按照 LSN（Log Sequence Number）的順序排列。從查詢的尾部可以查看 AllocUnitName 操作的數(shù)據(jù)表名稱。
對(duì)應(yīng)的 Operation 是?LOP_INSERT_ROWS，Context 是?LCX_HEAP，也就是插入數(shù)據(jù)到堆表。同時(shí)發(fā)現(xiàn) Page ID 是?0001:00000078，也就是十進(jìn)制的 120 號(hào)頁(yè)面。
可以使用 DBCC PAGE 命令查看 Page 頁(yè)信息。
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 語(yǔ)句 Insert 了數(shù)據(jù) Id = 1, Name = Dennis Gao, Phone = 88888888。
虛擬日志文件（VLF：Virtual Log File）
不管為事務(wù)日志定義多少個(gè)物理文件，SQL Server 總是把日志當(dāng)成連續(xù)流（Contiguous Stream）來對(duì)待。當(dāng) DBCC SHRINKDATABASE 命令確認(rèn)日志可以縮小多少時(shí)，它不是單獨(dú)考慮每個(gè)日志文件，而是根據(jù)整個(gè)日志來確定可壓縮大小。
SQL Server 數(shù)據(jù)庫(kù)的事務(wù)日志是通過 虛擬日志文件（VLF：Virtual Log File） 來管理的，VLF 的大小由 SQL Server 根據(jù)日志的總大小和日志增量大小來決定，不能通過配置指定。如果 VLF 數(shù)量變多會(huì)導(dǎo)致數(shù)據(jù)庫(kù)性能下降，所以需要指定合理的日志文件初始大小和增長(zhǎng)步長(zhǎng)，防止過多的?VLF 的產(chǎn)生。
SQL Server 會(huì)根據(jù)如下規(guī)則來判斷 VLF 的數(shù)量：
當(dāng)日志持續(xù)增長(zhǎng)時(shí)，會(huì)使用相同的方式確定新添加的 VLF 的數(shù)量。日志總是以整個(gè) VLF 為單位增長(zhǎng)，而且縮小也只能到 VLF 的邊界為止。
VLF 可以處于以下 4 種狀態(tài)之一。
- Active ：日志的活動(dòng)部分，從未提交事務(wù)的最小 LSN 開始，結(jié)束于最后一個(gè)寫入的 LSN。
- Recoverable ：在最早的活動(dòng)事務(wù)之前的那部分日志。
- Reusable ：如果日志已經(jīng)被備份，則不需要最早活動(dòng)事務(wù)之前的 VLF，可重用這些空間。日志截?cái)嗷騻浞輹?huì)將 Recoverable VLF 轉(zhuǎn)換成 Reusable VLF。
- Unused ：未使用的部分。
可以使用下面的 SQL 查詢 VLF 的數(shù)量。
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 命令進(jìn)一步觀察 VLF 的相關(guān)屬相。
DBCC
LOGINFO
SQL Server 可以配置多個(gè)物理日志文件當(dāng)做一個(gè)序列流來對(duì)待。如果管理良好，定期備份或截?cái)嗳罩荆赡苡肋h(yuǎn)都不會(huì)使用除第一個(gè)文件之外的其他日志文件。當(dāng)需要新的 VLF 時(shí)，多個(gè)物理文件中都沒有可用 VLF，則會(huì)以循環(huán)的方式把新的 VLF 添加到每個(gè)物理日志文件中。
自動(dòng)截?cái)嗄Ｊ剑ˋuto Truncate Model）
如果 SQL Server 設(shè)置了如下情況，則認(rèn)為沒有維護(hù)日志備份：
- 設(shè)置 SIMPLE 恢復(fù)模型，數(shù)據(jù)庫(kù)會(huì)定期截?cái)嗳罩尽?
- 從未進(jìn)行過完全數(shù)據(jù)庫(kù)備份。
以上任何一種情況下，SQL Server 會(huì)處于 自動(dòng)截?cái)嗄Ｊ剑ˋuto Truncate Model） 中，當(dāng)數(shù)據(jù)庫(kù)事務(wù)日志滿時(shí)就會(huì)進(jìn)行截?cái)唷＿@里的 "滿" 指的是日志記錄的數(shù)量比在系統(tǒng)啟動(dòng)過程中、在合理的時(shí)間內(nèi)能夠重做的數(shù)量多。
判斷數(shù)據(jù)庫(kù)是否在自動(dòng)截?cái)嗄Ｊ降淖詈?jiǎn)單的方法是查詢 sys.database_recovery_status 目錄視圖，如果 last_log_backup_lsn 列為空，則數(shù)據(jù)庫(kù)就是處于自動(dòng)截?cái)嗄Ｊ健?
SELECT
*
FROM
sys.database_recovery_status;
可以通過 DBCC SQLPERF 命名來查看日志文件大小。
DBCC
SQLPERF(
'
logspace
'
)
當(dāng)然，也可以通過系統(tǒng)提供的目錄視圖來查看。
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 命令來壓縮事務(wù)日志文件，下面是 DBCC SHRINKDATABASE 和? DBCC SHRINKFILE 的語(yǔ)法。
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 ?發(fā)表自 博客園 個(gè)人技術(shù)博客，未經(jīng)作者本人同意禁止任何形式的轉(zhuǎn)載，任何自動(dòng)或人為的爬蟲轉(zhuǎn)載或抄襲行為均為耍流氓。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
