SQL Server 事務日志文件縮減實操：恢復模式切換 + DBCC SHRINKFILE 釋放空間

問題現象

還原備份後發現日志文件 (LDF) 高達 120GB，SSMS 圖形界面縮減無效，需 T-SQL 強制處理。

核心步驟（生產環境謹慎操作）

1. 檢查恢復模式：
   

   SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'TargetDB';

   若為 FULL，必須先切換。
2. 切換至 SIMPLE 恢復模式（自動截斷非活動日志）：
   

   ALTER DATABASE TargetDB SET RECOVERY SIMPLE;

3. 縮減日志文件（目標縮至 10MB，視情況調整）：
   

   DBCC SHRINKFILE (N'TargetDB_log', 10); -- 邏輯文件名可用 sys.database_files 查詢

4. 驗證縮減結果：
   

   SELECT name, size * 8.0 / 1024 AS SizeMB FROM sys.database_files WHERE type_desc = 'LOG';

5. 切回 FULL 恢復模式（恢復日志鏈，重新建立備份基線）：
   

   ALTER DATABASE TargetDB SET RECOVERY FULL;

6. 立即執行完整備份（重新建立日志鏈起點，否則後續日志備份失敗）：
   

   BACKUP DATABASE TargetDB TO DISK = 'Path\Full.bak' WITH INIT, COMPRESSION;

關鍵原理

• FULL 模式下日志只能通過 BACKUP LOG 截斷，若長期未備份日志會無限增長。
• SIMPLE 模式下 Checkpoint 會自動截斷非活動 VLF，配合 DBCC SHRINKFILE 釋放物理空間。
• 切回 FULL 後必須立即完整備份，否則日志鏈斷裂，後續日志備份報錯「找不到上次日志備份」。

⚠️ 生產環境風險與替代方案

最佳實踐：預防勝於治療

1. 設置合理的 日志文件初始大小（預估峰值 1.2 倍）。
2. 配置 自動增長固定 MB 值（如 512MB），禁用百分比增長。
3. FULL 模式下 定期日志備份（每 15-60 分鐘），保持 VLF 循環復用。
4. 監控 sys.dm_db_log_space_usage、log_reuse_wait_desc，預警日志增長異常。