SQL Server 交易記錄檔暴增兩種清理方法與預防設定

現象

交易記錄檔（LDF）佔滿磁碟空間，導致資料庫無法寫入。需立即縮減記錄檔並調整復原模式防止復發。

方法一：標準清理流程（推薦，安全）

1. 截斷記錄（SQL 2000/2005 相容語法）：

   DUMP TRANSACTION 資料庫名 WITH NO_LOG;

   SQL 2005+ 建議改用：

   BACKUP LOG 資料庫名 WITH TRUNCATE_ONLY; -- 僅相容模式支援
   -- 或切換簡單模式後自動截斷

2. 縮減實體檔案：
   • 企業管理員 → 右鍵資料庫 → 所有工作 → 收縮資料庫 → 收縮檔案 → 選擇記錄檔 → 「收縮至」輸入建議最小值 → 確定。
   • 或 T-SQL：

     DBCC SHRINKFILE (N'資料庫名_log', 目標_MB);

方法二：分離/附加刪除 LDF（高風險，慎用）

1. 企業管理員 → 右鍵資料庫 → 分離資料庫。
2. 手動刪除實體 .ldf 檔案。
3. 右鍵資料庫 → 附加資料庫 → 僅選取 .mdf → 系統自動建立新 LDF（約 500KB）。

根本預防：設定簡單復原模式

簡單模式下，Checkpoint 時自動截斷無效記錄，LDF 不會無限增長。

圖形介面

企業管理員 → 右鍵資料庫 → 內容 → 選項 → 故障恢復模型 → 選擇「簡單」。

T-SQL

ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE;

輔助設定（SQL 2000 術語，2005+ 對應選項）

• Truncate log on checkpoint = 簡單復原模式（自動截斷）。
• Auto Shrink：定期檢查，未使用空間超過 25% 自動收縮（不建議長期開啟，會造成碎片）。

最佳實務建議

1. 生產環境務必使用完整復原模式 + 定期記錄備份（每 15-60 分鐘），兼顧時間點還原與 LDF 大小控制。
2. 僅開發/測試/唯讀資料庫使用簡單模式。
3. 監控 LDF 大小與 log_reuse_wait_desc，異常時立即處理。
4. 清理後檢查資料庫屬性是否被意外更改。