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 ?發表自 博客園 個人技術博客，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
