交易日誌是 SQL Server 資料庫恢復模型的核心，記錄每筆交易的所有修改操作，支援 ACID 特性、時間點還原、高可用性同步（Always On/鏡像/複寫/Log Shipping）。然而它常被忽視，導致日誌暴增、磁碟爆滿、資料庫掛起。本文系統整理日誌架構、維護操作、常見問題與最佳實踐。
交易日誌核心架構
- 實體結構：一或多個
.ldf 檔案，內部分為多個虛擬日誌檔 (VLF)，循環寫入。
- 邏輯結構：每筆交易產生一連串日誌記錄，包含交易 ID、操作類型 (LOP_INSERT_ROWS/LOP_MODIFY_ROW/LOP_DELETE_ROWS/LOP_BEGIN_XACT/LOP_COMMIT_XACT...)、前映像/後映像、頁面 ID、位移、長度等。
- 寫入機制：WAL (Write-Ahead Logging)——日誌記錄必須先於對應資料頁寫入磁碟（強制刷新到稳定介質），保證持久性。
- 檢查點：定期將 Dirty Pages 刷新至資料檔，並在日誌中標記檢查點 LSN，縮短復原時間、推進 VLF 可重用。
三種恢復模式與日誌行為差異
| 恢復模式 | 日誌記錄詳盡度 | 日誌截斷機制 | 支援功能 | 適用場景 |
|---|
| Simple | 大多操作最小記錄 | 檢查點自動截斷 | 僅完整/差異備份還原 | 測試/開發/資料倉儲 (可接受資料遺失) |
| Full | 所有操作完整記錄 | 僅交易日誌備份截斷 | 時間點還原、頁面還原、Always On、鏡像、異地複寫 | 生產 OLTP、關鍵業務 |
| Bulk-Logged | 大量操作最小記錄，其餘完整 | 日誌備份截斷 | 大部分 Full 功能，但大量操作無法時間點還原 | 大量匯入/索引重建維護窗口臨時切換 |
日誌維護核心操作
1. 交易日誌備份——Full/Bulk-Logged 模式強制要求
BACKUP LOG [DatabaseName] TO DISK = 'path\db_log_20240115_1400.trn'
WITH COMPRESSION, CHECKSUM, FORMAT, INIT, STATS = 10;
- 頻率：依 RPO 定義（如 15 分鐘/1 小時），不可中斷。
- 備份完成後，對應 VLF 狀態標記為
Inactive，可被覆寫/重用。
2. 日誌截斷 vs 壓縮——兩個完全不同的概念
| 操作 | 動作 | 釋放磁碟空間 | 典型指令 |
|---|
| 截斷 | 標記 VLF 為 Inactive，邏輯上可重用 | 否（檔案大小不變） | CHECKPOINT (Simple) / BACKUP LOG (Full) |
| 壓縮 | 物理移除檔案尾部未使用空間，縮減檔案大小 | 是 | DBCC SHRINKFILE (N, target_size) |
誤區警示：頻繁 SHRINKFILE 會導致 VLF 碎片化、檔案反覆成長效能暴跌。正確做法：適度預設初始大小/成長量，僅在異常暴增後一次性壓縮並固定大小。
3. VLF 管理最佳實踐
- 初始建立時指定合理大小（如 8GB），成長量固定值（如 512MB/1GB），禁用百分比成長。
- 過多 VLF (> 1000) 會拖慢啟動/還原/備份/Always On 同步。查詢：
DBCC LOGINFO。
- 若 VLF 過多：備份日誌 →
DBCC SHRINKFILE (N, 1) 壓至最小 → 重設目標大小 → 觀察 VLF 數量收斂。
常見問題與排查
| 現象 | 可能原因 | 對策 |
|---|
| Error 9002: Log file is full | 磁碟滿 / 自動成長受限 / 長時間交易阻塞截斷 / 復模式 Full 但無日誌備份 | 1. 釋放磁碟空間 2. 手動備份日誌 3. 檢查 log_reuse_wait_desc 找出阻塞原因 |
| 日誌檔持續暴增 | 長時間交易 / 大量操作 (未用 Bulk-Logged) / 異動複寫/Always On 同步延遲 / 客戶端未消費結果集 | 縮短交易範圍、維護窗口切 Bulk-Logged、監控同步延遲、修正應用邏輯 |
| VLF 碎片化嚴重 | 頻繁 Shrink/Autogrow 百分比成長 | 重建日誌檔：備份 → Shrink 到極小 → 修改檔案大小/成長量為固定值 |
效能優化關鍵
- 日誌檔放置於專用低延遲儲存（獨立 RAID-10/NVMe），分離資料檔與 TempDB I/O。
- 預設大小與固定成長量，避免執行期擴展阻塞工作負載。
- 啟用瞬間檔案初始化 (IFI)：授予服務帳戶
Perform Volume Maintenance Tasks，資料檔受益（日誌檔不適用，日誌必須零初始化）。
- 監控指標：
log_reuse_wait_desc、percent_log_used、VLF 數量、日誌寫入延遲 (sys.dm_io_virtual_file_stats)、交易產生日誌量/秒。
進階：Log Shipping 災難恢復架構
- 主伺服器：完整備份 + 定期日誌備份 → 複製至次要伺服器。
- 次要伺服器：
NORECOVERY / STANDBY 模式還原日誌，可供唯讀報表查詢。
- 監視伺服器：監控複製/還原作業狀態，逾時告警。
- RPO 可達分鐘級，RTO 視還原時間而定，成本遠低於 Always On。
掌握交易日誌機制，建立自動化備份/監控/告警體系，是 DBA 守護資料資產的根本功。
- 上一篇文章： MS SQL Server 7.0 儲存引擎架構設計目標與創新（經典技術白皮書摘要）
- 下一篇文章： SQL Server 複寫技術實戰：從概念到快照複寫逐步部署指南
Copyright ©
程式碼之家 All Rights Reserved
