SQL Server 交易記錄檔暴增？完整清理與收縮操作指南

問題背景

SQL Server 運行一段時間後，交易記錄檔（LDF）可能膨脹至數 GB，導致磁碟空間不足甚至服務掛掉。本文整理從輕量到重度的清理方案，並標註風險等級，助你安全瘦身。

⚠️ 重要原則：按步驟執行，切勿跳躍

• 步驟 4、6 風險較高，非必要勿用。
• 步驟 4（分離刪除 LDF）可能導致資料遺損。
• 步驟 6 限制檔案最大值，若達上限寫入將失敗，需清理後恢復。

方案一：標準清理流程（推薦，風險最低）

-- 1. 截斷記錄檔（簡單復原模式下有效）
DUMP TRANSACTION [資料庫名] WITH NO_LOG;
-- 或
BACKUP LOG [資料庫名] WITH NO_LOG;

-- 2. 收縮資料庫檔案
DBCC SHRINKDATABASE([資料庫名]);
-- 或指定檔案編號收縮（編號可用 SELECT * FROM sysfiles 查詢）
DBCC SHRINKFILE(1);  -- 資料檔
DBCC SHRINKFILE(2);  -- 記錄檔

企業管理器操作：右鍵資料庫 → 所有工作 → 收縮資料庫 → 選擇記錄檔/資料檔 → 「收縮至」輸入建議最小值。

方案二：極致瘦身——分離刪除 LDF 重建（高風險，慎用）

-- 1. 分離資料庫
EXEC sp_detach_db @dbname = '資料庫名';

-- 2. 在檔案系統手動刪除 .ldf 檔案

-- 3. 僅附加 .mdf，系統自動建立新 LDF（約 500KB）
EXEC sp_attach_single_file_db 
    @dbname = '資料庫名',
    @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\資料庫名.mdf';

企業管理器同樣可操作：右鍵資料庫 → 分離 → 刪除 LDF → 附加資料庫（僅選 .mdf）。

方案三：長效機制——自動收縮與成長限制

-- 開啟自動收縮（非生產環境建議關閉，會產生碎片與效能抖動）
EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE';

-- 限制記錄檔最大成長（單位 MB）
ALTER DATABASE [資料庫名] 
MODIFY FILE (NAME = 邏輯檔名, MAXSIZE = 2048);  -- 限制 2GB

企業管理器：右鍵資料庫 → 內容 → 選項 → 勾選「自動收縮」；交易記錄頁籤 → 檔案成長限制。

最佳實務建議

1. 修改復原模式：若不需時間點還原，改為 SIMPLE 模式，記錄檔自動截斷。
2. 定期完整備份 + 記錄備份：FULL 模式下唯有記錄備份才能截斷非作用段。
3. 預先規劃檔案大小：依業務量設定初始大小與成長增量（建議固定 MB，非百分比），減少自動成長頻率。
4. 監控與告警：設定 SQL Agent 作業定期檢查 DBCC SQLPERF(LOGSPACE)，超標發送通知。

小結

記錄檔暴增本質是備份策略與復原模式不匹配。優先採用方案一配合模式調整；方案二僅作緊急救援；方案三作為輔助手段。建立完善備份機制才是治本之道。