SQL Server 交易記錄核心概念深度解析：VLF、檢查點、截斷與收縮

四大核心概念

1. 實體記錄檔 (.ldf) — 容器

• 檔案系統層面的實體檔案，可單一或多檔 (檔案群)。
• 內部劃分為多個 **虛擬記錄檔 (VLF)**，非固定大小，由引擎自動管理。

2. 虛擬記錄檔 (VLF) — 管理單位

• 實體檔案內部的邏輯分段，**截斷與收縮的最小單位**。
• 例：400MB 實體檔 → 4 個 100MB VLF。收縮只能釋放完整 VLF (300MB、200MB)，無法釋放 239MB。
• 影響 VLF 數量/大小：初始大小、自動成長增量。建議：預先規劃大檔案、固定增量 (如 512MB/1GB)，減少 VLF 碎片、提升啟動/還原/備份速度。

3. 邏輯記錄 — 真實內容

• 實體檔案中按 **LSN (Log Sequence Number)** 順序寫入的記錄序列。
• 每筆交易：`BEGIN_XACT` → 資料修改記錄 → `COMMIT_XACT`/`ROLLBACK_XACT`。
• 分為兩區：
  • 作用中記錄：尚未被檢查點/備份截斷，包含未提交交易、已提交但未硬化/備份的交易。
  • 非作用中記錄：已提交、已硬化、已備份 (完整/大量記錄模式) 或已檢查點截斷 (簡單模式) 的記錄，可被覆寫。
• 關鍵標記：**MinLSN** — 最舊作用中記錄的 LSN，截斷/收縮的起點。

4. 檢查點 & 恢復間隔 — 記錄截斷觸發器

• 檢查點：將緩衝區髒頁刷新至資料檔，並將 MinLSN 推進 (標記對應記錄為非作用中)。
• 恢復間隔 (`recovery interval`)：伺服器層級設定 (預設 0 = 自動，約 1 分鐘)。系統估算「重做作用中記錄所需時間」接近此值時，自動觸發檢查點。
• **簡單復原模式**：檢查點直接截斷非作用中記錄 (MinLSN 推進，VLF 標記可重用)。
• **完整/大量記錄模式**：檢查點**不截斷**，必須執行 **記錄備份** 才能截斷 (MinLSN 推進至備份點)。

截斷 vs 收縮 — 兩碼事

**順序必嚴**：先截斷 (備份/檢查點) → 再收縮。若直接收縮而未截斷，作用中 VLF 在檔案中間，無法釋放。

常見指令速查

-- 1. 截斷記錄 (簡單模式隱含，完整模式需備份)
BACKUP LOG MyDB WITH NO_LOG;           -- 2005/2008 已移除，改用簡單模式暫時切換
ALTER DATABASE MyDB SET RECOVERY SIMPLE;
DBCC SHRINKFILE (MyDB_Log, 100);       -- 目標 100MB
ALTER DATABASE MyDB SET RECOVERY FULL;  -- 切回完整模式
-- 現代最佳實務：定期記錄備份 + 監控 VLF，避免手動收縮

-- 2. 查看 VLF 分布
DBCC LOGINFO (MyDB);  -- Status=0 閒置, 2 作用中

-- 3. 緊急收縮 (僅開發/測試環境，生產禁用)
-- 分離 → 刪除 .ldf → 附加單一 .mdf (自動建 500KB 新記錄檔)
-- 風險：未提交交易丟失、資料庫損壞、啟動失敗

記錄檔暴增根因診斷 (sys.databases.log_reuse_wait_desc)

最佳實務總結

1. **預先規劃大小**：依業務量設定初始大小 (如 10GB) + 固定增長 (1GB)，避免頻繁自動成長碎片化。
2. **完整模式 + 定期記錄備份** (每 15-60 分鐘) 是生產標準，自動截斷、可時間點還原。
3. **監控 `log_reuse_wait_desc` 與 VLF 數量** (建議 < 1000)，納入日常巡檢。
4. **拒絕常態化收縮**：收縮導致碎片、效能抖動、下次成長再耗資源。僅在異常暴增後一次性修正。
5. **長交易拆分**：大批次刪除/更新分批提交，避免單一交易撐爆記錄檔。