SQL Server 交易日志暴漲/滿載急救指南：收縮、清理、模式切換三步走

症狀與風險

• 錯誤 9002：The transaction log for database 'xxx' is full
• 資料庫掛起、寫入失敗、應用報錯
• 日志檔 (.ldf) 佔滿磁碟、甚至導致作業系統不穩

根因

1. 完整復原模式 下無定期記錄備份 → 日志無法截斷、持續增長
2. 長時間執行的交易 (大量批次、索引重建) 阻礙日志截斷
3. 複寫/鏡像/AlwaysOn 同步延遲導致日志積壓
4. autogrow 設定不當 (百分比增長、無上限)

急救三步走 (生產環境可接受短暫停機)

步驟 1：切換至簡單復原模式 → 強制截斷日志

-- 1. 切換復原模式 (需獨佔或無活動交易)
ALTER DATABASE [MyDB] SET RECOVERY SIMPLE WITH NO_WAIT;

-- 2. 執行 CHECKPOINT 強制截斷非作用中 VLF
CHECKPOINT;

-- 3. 驗證日志空間使用率
DBCC SQLPERF(LOGSPACE);  -- 查看 Log Space Used (%) 應大幅下降

原理：SIMPLE 模式下 CHECKPOINT 會標記非作用中虛擬日志檔 (VLF) 可重用，釋放邏輯空間。

步驟 2：收縮日志檔釋放物理空間

-- 查看邏輯檔名
USE MyDB;
SELECT name, size/128.0 AS SizeMB FROM sys.database_files WHERE type_desc = 'LOG';
-- 假設邏輯名為 MyDB_Log

-- 收縮至目標大小 (如 500MB)，需足夠可用空間
DBCC SHRINKFILE (N'MyDB_Log', 500);  -- 單位 MB

-- 或使用 GUI：SSMS 右鍵資料庫 → 工作 → 收縮 → 檔案 → 記錄檔 → 釋放未使用空間

⚠️ 收縮後立即執行索引維護 (重建/重組)，因收縮會產生嚴重碎片。

步驟 3：切回完整復原模式 → 立即完整備份 → 排程記錄備份

-- 1. 切回完整模式
ALTER DATABASE [MyDB] SET RECOVERY FULL;

-- 2. 立即完整備份 (建立新備份鏈起點)
BACKUP DATABASE [MyDB] TO DISK = 'N:\Backup\MyDB_FULL_POSTSHRINK.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

-- 3. 立即記錄備份 (可選，縮短 RPO)
BACKUP LOG [MyDB] TO DISK = 'N:\Backup\MyDB_LOG_POSTSHRINK.trn'
WITH COMPRESSION, CHECKSUM;

-- 4. 確認排程記錄備份作業已啟用 (每 15-60 分鐘)
-- SQL Agent → 作業 → 檢查/建立 "Backup Log - MyDB" 作業

進階方案：極端情況 (日志檔損壞、無法收縮)

分離附加重建日志 (高風險，最後手段)：

-- 1. 離線資料庫
ALTER DATABASE [MyDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- 或 SSMS 右鍵 → 工作 → 離線

-- 2. 分離
EXEC sp_detach_db 'MyDB';

-- 3. 重命名/刪除 .ldf 檔案 (備份副本以防萬一)

-- 4. 僅附加 .mdf (自動建立新 512KB/1MB 日志)
EXEC sp_attach_single_file_db @dbname = 'MyDB',
    @physname = 'D:\Data\MyDB.mdf';

-- 5. 立即完整備份、切回 FULL、排程記錄備份

⚠️ **風險**：未提交交易遺失、資料庫可能標記為 SUSPECT、複寫/鏡像中斷。僅限無備份、無複寫、可接受資料遺失的極端情況。

預防長效機制

常見誤區澄清

• ❌ BACKUP LOG ... WITH TRUNCATE_ONLY / NO_LOG：SQL 2008 已移除，無效
• ❌ 定期收縮日志 (排程 SHRINKFILE)：治標不治本、產生碎片、效能抖動
• ❌ 設定 MAXSIZE 限制日志大小：達上限後寫入失敗、資料庫掛起
• ✅ **正確之道**：FULL 模式 + 定期記錄備份 + 合理初始大小/增長 + 監控

總結流程圖

日志滿報警
    │
    ├─→ 確認復原模式 = FULL 且無記錄備份排程？
    │       ├─ 是 → 切 SIMPLE → CHECKPOINT → SHRINKFILE → 切 FULL → 完整備份 → 建立記錄備份排程
    │       └─ 否 → 檢查長交易/複寫延遲/磁碟空間 → 針對性處理
    │
    └─→ 事後複盤：根因分析、修正配置、更新運行手冊

日志管理是 SQL Server DBA 的基本功。**預防永遠優於急救**，請今天就檢查您的記錄備份排程是否正常運行。