交易記錄檔無限增長是 SQL Server 維護高頻問題。本文提供三種清空方案:標準備份截斷法 (生產首選)、簡單復原模式自動管理 (開發/測試)、分離刪除重建法 (緊急/高風險),並解析自動收縮與截斷選項的運作機制。
BACKUP LOG [資料庫名] WITH NO_LOG
-- SQL 2008+ 已移除 NO_LOG,改用:
BACKUP LOG [資料庫名] TO DISK = 'NUL:' -- 僅測試環境
-- 正規生產環境應備份至磁碟:
BACKUP LOG [資料庫名] TO DISK = 'D:\Backup\DB_Log.trn'
將復原模式從「完整」改為「簡單」,記錄檔在 **Checkpoint (檢查點)** 時自動截斷,不再無限增長。
ALTER DATABASE [資料庫名] SET RECOVERY SIMPLE;
ALTER DATABASE [資料庫名] SET RECOVERY FULL;
| 選項 | 相容版本 | 行為 |
|---|---|---|
| Truncate log on checkpoint | SQL 7.0 / 2000 (相容性層級) | Checkpoint 時若記錄檔 > 70% 使用率,自動截斷。相當於簡單模式 |
| Auto Shrink | 所有版本 | 定期檢查,未用空間 > 25% 時自動收縮。不建議開啟:造成索引碎片、IO 尖峰、效能抖動 |
⚠️ **警告:可能導致未提交交易資料遺失,僅限非生產或已接受風險**
DBCC SQLPERF(LOGSPACE) 定期檢查使用率DBCC OPENTRAN 找出阻塞截斷的活躍交易DBCC OPENTRAN)sp_helpfile)SQL Server 記錄檔管理鐵律:
🟢 **生產環境**:完整復原模式 + 定期 LOG 備份 + 定期收縮 (維護窗口)
🟡 **開發/測試**:簡單復原模式 + 偶爾手動收縮
🔴 **緊急/最後手段**:分離刪除 LDF 重建 (接受資料遺失風險)
⚙️ **長效**:固定自動成長 + 預分配 + 監控 + 長交易偵測