SQL Server 交易記錄檔完整管理指南：原理、維護、壓縮與災難恢復

交易記錄檔（LDF）是 SQL Server 確保 ACID 特性的核心，卻常因疏忽維護導致暴漲佔滿磁碟、資料庫標記 SUSPECT 甚至服務中斷。本文系統整理記錄檔運作原理、日常維護、壓縮技術、災難恢復與效能優化最佳實務。

核心運作機制

• 預寫日誌（WAL）：所有修改先寫入記憶體緩衝區並同步寫入 LDF，再由 Checkpoint 背景執行緒批次刷新至資料檔（MDF/NDF）。
• 虛擬記錄檔（VLF）：LDF 內部分為多個 VLF，循環使用；截斷（Truncate）僅標記 VLF 可重用，不釋放實體空間。
• 復原模式決定截斷時機：
  • Simple：Checkpoint 時自動截斷。
  • Full / Bulk-Logged：需 BACKUP LOG 才會截斷。

日常維護三部曲

1. 定期備份記錄檔（Full/Bulk-Logged 模式必須）：
   BACKUP LOG DB_Name TO DISK = 'path.trn' WITH COMPRESSION
2. 監控記錄檔使用率：
   DBCC SQLPERF(LOGSPACE)、SELECT name, log_reuse_wait_desc FROM sys.databases
3. 設定合理自動成長：避免 10%/1MB 預設值，改為固定增量（如 512MB/1GB）並啟用 MAXSIZE 上限。

記錄檔暴漲緊急處理（四級方案）

災難恢復：Log Shipping 架構

• 主伺服器定期 BACKUP LOG → 複製至備援伺服器 → RESTORE LOG WITH NORECOVERY。
• 監視伺服器偵測複製間隔超時 → 發出警示。
• 提供資料庫級熱備援，RPO 可達分鐘級，適合無法容忍資料遺失的核心系統。

效能優化關鍵

1. 記錄檔置於獨立磁碟/RAID 1+0：順序寫入特性，避免與資料檔隨機 I/O 爭搶。
2. 預先配置足夠初始大小：減少自動成長頻率，避免成長過程阻塞交易。
3. 避免長時間交易：大量匯入用 BULK INSERT/BCP 搭配 Bulk-Logged 模式，分批提交。
4. 啟用即時檔案初始化（IFI）：資料檔受益，記錄檔仍需零填充（安全性考量）。

自動收縮：請勿開啟

AUTO_SHRINK = ON 會導致反覆縮放產生碎片、CPU 飆升、效能抖動，微軟已標記為棄用功能，生產環境嚴禁啟用。

快速檢查清單

• [ ] 復原模式符合 RPO 需求？
• [ ] 記錄檔備份排程正常執行？
• [ ] 自動成長設為固定增量且有上限？
• [ ] 記錄檔在獨立磁碟？
• [ ] 監控 log_reuse_wait_desc 非 NOTHING 時有告警？
• [ ] 定期演練時間點還原？

善管記錄檔，才能讓 SQL Server 在高併發、大交易量下持穩定運行。