您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 交易記錄檔完整管理指南：架構、維護、截斷、壓縮與效能調優

編輯：關於SqlServer

交易記錄檔架構

每筆交易的**啟動、資料修改、提交/回滾**皆順序寫入記錄檔（LDF）。記錄包含：交易 ID、LSN (Log Sequence Number)、操作類型、前映像/後映像、頁面 ID、槽位等。SQL Server 採用 **WAL (Write-Ahead Logging)**：記錄先寫入記錄檔（硬化到磁碟），再由 Checkpoint 背景執行緒將髒頁寫入資料檔 (MDF/NDF)。

虛擬記錄檔 (VLF) 與截斷

實體記錄檔內部分割為多個 VLF。截斷只標記 VLF 為「非作用中」，**不釋放磁碟空間**。截斷觸發條件：

簡單復原模式：Checkpoint 時自動截斷。
完整/大量記錄模式：執行 `BACKUP LOG` 後截斷。
異常狀態：長時間執行交易、複寫/鏡像/AlwaysOn 同步延遲、CDC 掃描滯後、資料庫鏡像暫停 → 阻礙截斷，導致記錄檔暴增。

記錄檔壓縮

壓縮才真正釋放作業系統空間（移除檔案尾端未使用的 VLF）。

-- 壓縮整個資料庫（含資料檔）
DBCC SHRINKDATABASE (DBName, TRUNCATEONLY); -- 僅釋放尾端空間，不移動頁面
-- 壓縮特定記錄檔
DBCC SHRINKFILE (DBName_Log, 1024); -- 目標大小 1GB，單位 MB

最佳實踐：先 `BACKUP LOG` 觸發截斷，再 `SHRINKFILE` 至合理大小（如預估高峰期記錄量的 1.5-2 倍）。**避免排程自動壓縮**（`AUTO_SHRINK ON`），會造成索引碎片、CPU 飆升、效能抖動。

復原模式選擇與備份策略

模式	記錄備份	時間點還原	適用場景
簡單	不支援	不支援	開發/測試、資料可重建、RPO ≥ 1 天
完整	必要	支援	生產環境、RPO 分鐘級、需時間點還原
大量記錄	必要	支援（大量作業除外）	大量匯入/索引重建期間臨時切換，降低記錄量

記錄檔效能調優

專屬磁碟/磁碟群：記錄檔屬循序寫入，放置於獨立 RAID 1+0 (HDD) 或 NVMe (SSD)，避免與資料檔隨機 I/O 爭用。
預先配置大小、固定成長增量：避免頻繁自動成長（預設 10%/10MB）造成 I/O 暫停與 VLF 碎片。建議初始大小 = 預估峰值，成長固定 512MB/1GB。
控制 VLF 數量：過多 VLF (<100) 延長啟動恢復、備份/還原、CDC 掃描時間。`DBCC LOGINFO` 檢查，必要時縮小後重新成長至目標大小以重組 VLF。
監控關鍵指標：`log_reuse_wait_desc` (sys.databases)、`Percent Log Used` (PerfMon)、`Log Flush Wait Time`、`Log Growths`。

常見問題排查

症狀	可能原因	對策
記錄檔暴增佔滿磁碟	長交易、未備份記錄、複寫/鏡像延遲、CDC 滯後	查 `log_reuse_wait_desc`，針對性處理（提交/回滾長交易、補記錄備份、修復複寫）
還原 `WITH NORECOVERY` 卡住	記錄鏈斷裂、LSN 不連續	補齊遺漏的記錄備份，或從完整備份重新開始
`SHRINKFILE` 不縮小	尾端 VLF 仍作用中、最小檔案尺寸限制	先 `BACKUP LOG`，確認 `DBCC LOGINFO` 尾端 Status=0，再壓縮