SQL Server 交易記錄檔核心角色：WAL 機制、一致性保證與 VLF 管理

交易記錄檔 (LDF) 是 SQL Server 可靠性的基石。本文從 ACID 一致性需求 切入，解析 Write-Ahead Logging (WAL) 如何保證 原子性 與 持久性，並說明 VLF (Virtual Log File) 結構與管理要點。

為何需要記錄檔？一致性定義

• 資料庫一致性：滿足所有顯式約束 (PK, FK, Check, Unique) 與隱式業務規則 (觸發器/應用層保證)。
• 交易一致性：若交易執行期間無系統錯誤，且起始狀態一致，則結束時狀態仍一致。
• 原子性：交易不可部分執行 (All or Nothing)。
• 系統故障場景：電源中斷、OS Crash、SQL 進程異常結束 → 記憶體髒頁遺失。若無記錄檔，無法知曉哪些操作已完成、哪些未完成，亦無法 Redo/Undo。

WAL 核心原則

1. 任何資料修改 (INSERT/UPDATE/DELETE) 先寫入記憶體 Log Buffer，產生記錄檔記錄 (Log Record)，含 LSN、Transaction ID、前映像/後映像、操作類型。
2. Commit 時強制將 Log Buffer Flush 至磁碟 (LDF) (同步 I/O)，確保記錄持久化。
3. 資料頁 (髒頁) 由 Checkpoint / Lazy Writer 非同步寫入磁碟 (MDF/NDF)。
4. 故障恢復：
    - Redo (重做)：從最後 Checkpoint LSN 向前重放所有已 Commit 交易的記錄，確保持久性。
    - Undo (回滾)：回滾未 Commit 交易的修改，確保原子性。

記錄檔內部結構：VLF

• LDF 邏輯上劃分為多個 Virtual Log Files (VLF)，大小由建立/自動成長時決定。
• 記錄檔以循環方式寫入 VLF：寫滿一個 → 標記 Active → 切換下一個。
• VLF 狀態：0 = Free (可覆寫), 1 = Active (含未截斷記錄)。
• 查看 VLF：DBCC LOGINFO。

VLF 管理最佳實務

1. 避免過多 VLF ( > 1000) 導致啟動恢復緩慢、備份/還原耗時。
    - 原因：頻繁小幅度自動成長 (如 1MB/10%) 產生大量微小 VLF。
    - 解法：預先調整記錄檔至目標大小 (如 10~50 GB)，固定成長量 512MB~1GB，而非百分比。
2. 避免過少/過大 VLF：單一巨大 VLF 導致截斷粒度粗、空間浪費。
3. 監控：SELECT COUNT(*) FROM sys.dm_db_log_space_usage WHERE database_id = DB_ID() (SQL 2012+)。
4. 縮減記錄檔：DBCC SHRINKFILE (N'MyDB_Log', 10240) (目標 10GB)，先確保 VLF 多為 Free (執行記錄備份/Checkpoint)。

記錄檔相關常見誤區

• 誤區：TRUNCATE TABLE 不記錄。事實：記錄頁面釋放操作 (最小日誌)，可 Rollback。
• 誤區：Simple 模式不需記錄備份，記錄檔不會增長。事實：長交易未結束時，VLF 無法截斷，記錄檔仍會暴增。
• 誤區：刪除 LDF 檔案可縮小資料庫。事實：導致資料庫 SUSPECT，極難恢復 (需單一使用者模式重建記錄檔)。