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