SQL Server 交易記錄檔過大怎麼辦？四種縮減與預防方案完整解析

情境

資料庫 test2 的交易記錄檔 (LDF) 異常膨脹，影響磁碟空間與效能。以下提供四種處理策略，由治標到治本。

方法一：分離資料庫 → 重命名 LDF → 附加單一 MDF（適用 7.0/2000）

1. EXEC sp_detach_db 'DB_Name'
2. 在檔案系統將 xxx_Log.LDF 改名備份
3. EXEC sp_attach_single_file_db 'DB_Name', 'C:\...\DB_Name.MDF'
4. 成功則刪除舊 LDF；失敗則改回檔名，改用 sp_attach_db 重新附加，再嘗試方法二

方法二：截斷記錄再收縮（依版本指令不同）

SQL Server 6.x

DUMP TRANSACTION test2 WITH NO_LOG
DUMP TRANSACTION test2 WITH TRUNCATE_ONLY
-- 重複執行直到記錄檔縮小

SQL Server 7.0 / 2000

BACKUP LOG test2 WITH NO_LOG
BACKUP LOG test2 WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE(test2)
-- 重複執行直到記錄檔縮小

缺點： 此法僅治標，記錄檔會隨後續交易再次膨脹。

方法三：設為簡單復原模式 + 自動收縮（適合測試環境）

-- 6.x/7.0：截斷模式；2000：簡單復原模式
EXEC sp_dboption 'test2', 'trunc. log on chkpt.', 'true'
-- 7.0/2000 啟用自動收縮
EXEC sp_dboption 'test2', 'autoshrink', 'true'

方法四：完整復原模式 + 定期記錄備份 + 自動收縮（生產環境建議）

-- 7.0/2000 關閉截斷模式 → 完整復原
EXEC sp_dboption 'test2', 'trunc. log on chkpt.', 'false'
EXEC sp_dboption 'test2', 'autoshrink', 'true'

-- 建立排程作業：
-- 每 30 分鐘備份一次交易記錄
-- 每天完整資料庫備份一次
-- 記錄檔收縮至正常大小後，將 autoshrink 設為 false

注意： 生產系統長期開啟 autoshrink 可能在繁忙時段觸發收縮，造成效能抖動；僅建議在無專職 DBA 的桌面/遠端系統啟用。

補充知識：什麼時候記錄檔實體大小會減少？

• 執行 DBCC SHRINKDATABASE 或針對記錄檔的 DBCC SHRINKFILE
• 自動收縮作業發生時

記錄收縮依賴「記錄截斷」：截斷不減少實體大小，只將非作用中虛擬記錄檔 (VLF) 標記為可重用；收縮才會實際釋放空間。

控制記錄檔大小的兩大原則

1. 維護記錄備份鏈 → 排程 BACKUP LOG 定期執行
2. 不需記錄備份鏈 → 設定簡單復原模式