三種縮減 SQL Server 交易記錄檔大小的實用方法

現象與風險

交易記錄檔（LDF）若長期未備份或資料庫處於「完整復原模式」下，會持續增長，佔用大量磁碟空間，甚至導致資料庫無法寫入。本文提供三種經驗證有效的縮減方案。

方法一：備份記錄 + TRUNCATE_ONLY + SHRINKDATABASE（適用舊版 SQL Server 2000/2005）

-- 假設資料庫名為 MuOnline
BACKUP LOG MuOnline WITH NO_LOG;
BACKUP LOG MuOnline WITH TRUNCATE_ONLY;
DBCC SHRINKDATABASE(MuOnline);

-- 重複執行上述三行，直到記錄檔縮小至預期大小
-- 臨時開啟自動收縮（僅應急）
EXEC sp_dboption 'MuOnline', 'autoshrink', 'on';

-- 建立維護作業：每 30 分鐘記錄備份，每天完整備份
-- 記錄檔恢復正常大小後，關閉自動收縮
EXEC sp_dboption 'MuOnline', 'autoshrink', 'off';

注意：SQL Server 2008 及以上版本已移除 TRUNCATE_ONLY，請改用方法二或三。

方法二：切換至簡單復原模式並收縮（最簡單、通用）

1. 在企業管理器/SSMS 中，右鍵資料庫 → 「屬性」 → 「選項」 → 「復原模式」選擇 簡單。
2. 右鍵資料庫 → 「所有工作」 → 「收縮資料庫」 → 「確定」。
3. 完成後可視需求切回「完整模式」，並立即進行完整備份以啟動記錄鏈。

簡單模式下，記錄檔會在檢查點自動截斷，不再無限增長。

方法三：分離資料庫、刪除 LDF、重新附加（極端情況最後手段）

1. 企業管理器 → 右鍵資料庫 → 「所有工作」 → 「分離資料庫」。
2. 至資料庫檔案目錄，將 MuOnline_log.LDF 重命名或移動備份（勿直接刪除，以防萬一）。
3. 企業管理器 → 右鍵「資料庫」 → 「附加資料庫」 → 選取 MuOnline.mdf。系統會提示找不到記錄檔，詢問是否建立新記錄檔，選「是」。
4. 重新附加後，需重新設定資料庫使用者權限（sp_change_users_login 或重新對應登入）。

風險提示：此方法會導致未提交的交易遺失，且破壞記錄鏈，必須立即進行完整備份。僅在記錄檔損壞或空間極度不足且無法備份時使用。

最佳實踐建議

• 生產環境務必使用「完整復原模式」+ 定期記錄備份（如每 15-30 分鐘）。
• 監控記錄檔使用率（DBCC SQLPERF(LOGSPACE)），設定警示。
• 避免長時間執行的大量交易未提交，導致記錄檔無法截斷。