刪除 SQL Server 大量交易記錄檔三大方案：分離刪除、BACKUP LOG NO_LOG、收縮與自動成長控制

交易記錄檔 (LDF) 暴增佔滿磁碟是經典營運事件。本文提供三種清理方案，依風險與適用場景分級。

方案一：分離資料庫刪除 LDF (高風險、極端急救)

1. EXEC sp_detach_db 'DBName';
2. 手動刪除 DBName_log.ldf。
3. EXEC sp_attach_single_file_db 'DBName', 'Path\DBName.mdf'; — SQL Server 自動建立最小 512KB 新記錄檔。

風險：未提交交易遺失、資料庫損毀無法附加、複寫/鏡像/AlwaysOn 拓撲破壞。**僅限無備份可還原、極端情況，且必須先驗證完整備份可還原。**

方案二：標準收縮流程 (安全、線上)

1. 簡單復原模式：ALTER DATABASE DBName SET RECOVERY SIMPLE;
2. BACKUP LOG DBName WITH NO_LOG; (SQL 2005/2008) 或直接 DBCC SHRINKFILE (LogFileName, TargetMB);
3. SSMS → 工作 → 收縮 → 檔案 → 記錄檔 → 「收縮至」目標 MB (建議保留 10~20% 餘裕)。
4. 恢復完整模式 (需時間點還原)：ALTER DATABASE DBName SET RECOVERY FULL; 並**立即執行完整備份**啟動記錄鏈。

方案三：預防長大 — 自動成長與收縮設定

• 自動成長固定值：ALTER DATABASE DBName MODIFY FILE (NAME = LogFileName, FILEGROWTH = 512MB); 避免 10%/1MB 碎片化。
• 限制最大大小：ALTER DATABASE DBName MODIFY FILE (NAME = LogFileName, MAXSIZE = 50GB); 防止佔滿磁碟。
• 簡單模式 + 定期完整/差異備份：記錄檔自動截斷復用，不會無限增長。
• 完整模式 + 高頻記錄備份 (每 15~60 分鐘)：BACKUP LOG DBName TO DISK = '...'; 記錄檔空間循環使用。
• AUTO_SHRINK = OFF (預設)：生產環境**嚴禁開啟**，會造成效能抖動、索引碎片、計畫失效。

監控與告警

-- 記錄檔使用率
DBCC SQLPERF(LOGSPACE);
-- 設定 Agent 警示：Log Space Used > 80% 發送郵件

結語

LDF 暴增根因在於「復原模式與備份策略不匹配」或「自動成長設定不當」。標準收縮 + 合理自動成長 + 正確備份策略 = 根治。分離刪除 LDF 僅作最後手段，風險極高。