SQL Server 交易記錄檔總結與記錄檔滿異常完整處理指南

交易記錄檔核心角色

• 記錄**所有**資料庫修改操作 (含系統內部操作)，保證 **ACID**。
• **寫前記錄 (WAL)**：修改先寫記錄緩衝區 → 強制刷入記錄檔 (硬化) → 才寫資料檔 (延遲寫入/Checkpoint)。
• 重啟恢復：掃描記錄檔，**REDO** (重做) 已提交未硬化交易，**UNDO** (回滾) 未提交交易，確保資料庫一致狀態啟動。

維護三部曲：備份 → 截斷 → 收縮

1. 備份：完整模式下 `BACKUP LOG` 將非作用中記錄標記可重用 (MinLSN 推進)。
2. 截斷：邏輯操作，不減小實體檔案。**簡單模式**由 Checkpoint 自動截斷；**完整模式**需記錄備份觸發。
3. 收縮：`DBCC SHRINKFILE` 釋放檔案末端**完整閒置 VLF** 歸還 OS，**實體大小減小**。**必須先截斷才能收縮**。

還原策略：資料檔 + 記錄檔鏈

• 先還原完整備份 (`WITH NORECOVERY`)。
• 依序還原差異備份 (最新一份) → 記錄備份 (按時間順序，`WITH NORECOVERY`)。
• 最後 `RESTORE DATABASE MyDB WITH RECOVERY` 完成線上。
• Log Shipping：自動化上述流程至備援伺服器，加入監視伺服器告警，構建災難恢復熱備份。

效能優化兩大原則

1. 記錄檔置於獨立磁碟/RAID 1+0：順序寫入特性，避免與資料檔隨機 IO 爭搶。
2. 合理設定初始大小與固定增長：避免頻繁自動成長 (預設 10% 易導致大檔案成長卡頓、阻塞)。建議：初始 = 預估峰值 70%，增長 = 固定 512MB/1GB。

錯誤 9002：記錄檔已滿 — 根因與解決

常見根因 (查詢 `sys.databases.log_reuse_wait_desc`)

記錄檔滿兩種解法 (按風險排序)

方案一：標準安全流程 (推薦生產)

1. 查詢分析器：`DUMP TRANSACTION MyDB WITH NO_LOG` (SQL 2000/2005) 或 `ALTER DATABASE MyDB SET RECOVERY SIMPLE; CHECKPOINT;` (2008+)。
2. SSMS：右鍵資料庫 → 工作 → 收縮 → 檔案 → 記錄檔 → 收縮至最小可用值。
3. 立即執行完整備份 + 記錄備份，切回完整模式 (若原為完整模式)。

方案二：高風險極速流程 (僅限非生產/有完整備份可重建)

1. 分離資料庫：`EXEC sp_detach_db 'MyDB';`
2. 刪除實體 `.ldf` 檔案。
3. 附加單一資料檔：`EXEC sp_attach_single_file_db @dbname='MyDB', @physname='D:\Data\MyDB.mdf';`
4. 自動產生 500KB 新記錄檔。

警告：方案二會丟失未提交交易、可能導致資料庫不一致 (SUSPECT)、啟動失敗。生產環境**嚴禁**使用。

預防性設定檢查清單

• [ ] 復原模式 = 完整 (關鍵業務) + 記錄備份排程 ≤ 60 分鐘。
• [ ] 記錄檔初始大小 ≥ 預估正常運作峰值，固定增長 512MB/1GB (非 10%)。
• [ ] 設定 `MAXSIZE` 防止佔滿磁碟 (或啟用磁碟空間告警)。
• [ ] `AUTO_SHRINK = OFF` (預設)，納入維護計劃手動控制。
• [ ] 監控 `log_reuse_wait_desc`、`VLF 數量`、`記錄檔使用率%` 納入每日巡檢。