SQL Server 交易記錄完整指南：架構原理、維護策略、滿載處理與效能優化

交易記錄核心架構

交易記錄（Transaction Log，LDF）記錄資料庫所有修改操作，是**恢復機制、複寫、鏡像、AlwaysOn 的基石**。關鍵特性：

• **順序寫入、不讀取**：高效能循序 I/O。
• **WAL（Write-Ahead Logging）**：修改先寫記錄、再寫資料頁（Checkpoint 時）。
• **虛擬記錄檔（VLF）**：實體檔案內部分為多個 VLF，循環覆寫。
• **LSN（Log Sequence Number）**：每筆記錄唯一遞增序號，保證順序與一致性。

復原模式對記錄截斷的影響

日常維護三板斧

1. 定期記錄備份（完整/大量記錄模式必備）

頻率視 RPO 而定（如 15-60 分鐘）。備份會標記非作用 VLF 可覆寫，**不會縮小實體檔案大小**。

2. 記錄檔壓縮

-- 壓縮指定記錄檔至目標大小（MB）
DBCC SHRINKFILE (N'DBName_log', 512);

最佳實踐：先備份、再壓縮；避開業務尖峰；壓縮後檔案大小應 ≥ 正常運行高水位，避免頻繁自動增長碎片化。

3. 合理設定自動增長

• **固定增量（MB）優於百分比**：如 512MB/1GB，避免指數級增長導致效能抖動。
• **預先擴大至預估高水位**：減少增長次數，避免零初始化等待（啟用瞬間檔案初始化可加速資料檔增長，記錄檔仍需零初始化）。
• **啟用上限限制**：防止佔滿磁碟導致資料庫掛起。

記錄檔滿載（Error 9002）常見原因與對策

緊急處理：記錄檔已滿、資料庫掛起

方法一：標準流程（推薦，資料零遺失）

1. 執行記錄備份：BACKUP LOG DBName TO DISK='path\log.trn' WITH NO_TRUNCATE;
2. 壓縮記錄檔：DBCC SHRINKFILE (DBName_log, 目標MB);
3. 檢查復原模式、備份排程、自動增長設定，防止復發。

方法二：分離刪除 LDF 重建（高風險，可能遺失資料）

1. 分離資料庫。
2. 刪除/改名 LDF 檔案。
3. 附加僅含 MDF 的資料庫，SQL Server 自動建立最小記錄檔（~512KB）。

⚠️ **僅限簡單模式、確認無未提交交易、可接受近期資料遺失時使用。**

效能優化關鍵點

1. 記錄檔置於獨立磁碟/RAID 1+0：順序寫入無隨機 I/O 干擾，IOPS 需求高。
2. 預先適當調大：減少自動增長頻率與零初始化延遲。
3. 避免長交易：分批提交、優化索引減少鎖持有時間。
4. 監控 VLF 數量：過多（>1000）或過少（<50）皆影響啟動/備份/還原速度。DBCC LOGINFO 檢視。

結語

交易記錄是 SQL Server 可靠性的核心。**正確復原模式 + 規律記錄備份 + 合理自動增長 + 獨立儲存**四大支柱缺一不可。遇 9002 錯誤先冷靜分析原因，優先採標準備份+壓縮流程，切勿輕易刪除 LDF。