SQL Server 交易日誌暴增清理：兩種縮減方案與預防最佳實務

現象與成因

交易日誌 (.ldf) 佔滿磁碟導致資料庫寫入失敗，常見原因：

• 復原模式為「完整」但未定期備份交易日誌，導致非作用中 VLF (Virtual Log File) 無法截斷重用。
• 長時間執行的大量交易 (大批次匯入、索引重建) 未提交/回滾。
• 複寫/鏡像/AlwaysOn 同步延遲導致日誌無法截斷。
• log_reuse_wait_desc 顯示 LOG_BACKUP、ACTIVE_TRANSACTION、REPLICATION 等等待原因。

方案一：標準縮減流程 (推薦、安全、可控)

1. 備份交易日誌 (使非作用中 VLF 可截斷)：

   BACKUP LOG [YourDB] TO DISK = 'D:\Backup\YourDB_Log.trn' WITH COMPRESSION, STATS = 5;

2. 縮減日誌檔案 至目標大小 (需小於目前配置大小)：

   -- 以 SSMS 圖形化：右鍵資料庫 → 工作 → 縮減 → 檔案 → 檔案類型「記錄」 → 釋放未使用空間 / 縮減至 N MB
   -- 或 T-SQL：
   DBCC SHRINKFILE (N'YourDB_Log', 1024); -- 縮減至 1 GB (單位 MB)

3. 驗證：DBCC SQLPERF(LOGSPACE); 確認使用率回落合理區間 (10-30%)。

⚠ 注意：頻繁縮減會導致日誌檔反覆增長/縮減 (VLF 碎片化)，嚴重影響寫入效能。縮減應為「一次性急救」，而非常態操作。

方案二：分離/附加重建日誌 (高風險、極速、僅限緊急)

⚠ 高風險警告：此操作會**丟失未備份的交易日誌內容**，可能導致資料不一致、違反 RPO。僅在「日誌檔損壞、無法備份、接受資料遺失」極端情況使用，且必須先離線備份 .mdf/.ldf 檔案。

1. SSMS → 右鍵資料庫 → 工作 → **分離** (勾選「卸除連線」)。
2. 檔案總管 → 刪除/重命名 .ldf 檔案。
3. SSMS → 右鍵「資料庫」 → **附加** → 新增 .mdf → 確定 (自動建立新 512 KB 日誌檔)。
4. 立即執行 完整備份 建立新復原基準點。

根治預防：正確配置復原模式與備份策略

1. 選擇適當的復原模式

2. 設定簡單模式 (若業務允許)

-- T-SQL
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
GO
-- SSMS：右鍵資料庫 → 內容 → 選項 → 復原模式 → 簡單

3. 建立自動化日誌備份作業 (完整模式必備)

-- 維護計畫 / SQL Agent Job 每 15-30 分鐘執行
BACKUP LOG [YourDB] 
TO DISK = 'D:\Backup\YourDB_Log_%Y%m%d_%H%M.trn'
WITH COMPRESSION, CHECKSUM, STATS = 5;
-- 配合清理舊備份腳本 (保留 7-30 天)

4. 監控與告警

• DBCC SQLPERF(LOGSPACE); 定期收集日誌使用率，超過 70% 觸發告警。
• 監控 log_reuse_wait_desc (sys.databases) 非 NOTHING 狀態持續超過閾值告警。
• 磁碟剩餘空間 < 20% 告警。

進階優化：VLF 管理與預設大小設定

• 避免日誌檔頻繁自動成長 (預設 10%/10MB) 產生大量小 VLF → 影響啟動恢復、備份/還原、複寫效能。
• **預先配置足夠大小** (預估峰值業務日誌量 × 1.5) + **固定成長量** (如 512MB/1GB) 而非百分比。
• 查詢 VLF 分布：DBCC LOGINFO; 理想 VLF 數量 < 1000，大小均勻。

結語

日誌暴增本質是「備份策略缺失」或「復原模式配置錯誤」。**標準方案 (日誌備份 + 縮減)** 安全可控，應作為首選；**分離重建** 僅作極端急救。根治在於：正確選擇復原模式、建立自動化日誌備份作業、預先配置日誌大小與固定成長量、部署監控告警。將日誌管理納入常態運維體系，才能徹底杜絕「半夜磁碟滿」驚魂。