SQL Server 2008 R2 清理資料庫記錄檔 (LDF) 縮小完整步驟

適用場景

• 記錄檔佔用大量磁碟空間，影響備份/還原速度。
• 復原模式為 完整 (FULL) 但未定期進行記錄備份，導致 LDF 無限增長。
• 需快速釋放空間且接受暫時中斷記錄鏈。

核心原理

1. 切換至 SIMPLE 復原模式 → 截斷非作用中 VLF。
2. DBCC SHRINKFILE 釋放實體空間至作業系統。
3. 切回 FULL 模式 → 立即進行完整備份重建記錄鏈。

標準腳本 (請替換資料庫名稱與邏輯檔名)

USE [master];
GO
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
GO

USE [YourDB];
GO
-- 邏輯記錄檔名稱可用下方查詢取得
DBCC SHRINKFILE (N'YourDB_Log', 0, TRUNCATEONLY);
GO

USE [master];
GO
ALTER DATABASE [YourDB] SET RECOVERY FULL WITH NO_WAIT;
GO
ALTER DATABASE [YourDB] SET RECOVERY FULL;
GO

-- 查詢邏輯記錄檔名稱
USE [YourDB];
GO
SELECT name FROM sys.database_files WHERE type_desc = 'LOG';

參數說明

• TRUNCATEONLY：僅釋放檔案末端未使用空間，不移動資料頁，不造成碎片，速度最快。
• 目標大小 0：表示縮減至預設最小值；若需保留特定大小 (MB) 可改為數字，如 1024。
• WITH NO_WAIT：不等待長時間交易完成，立即切換模式 (可能導致交易回滾，請在維護窗口執行)。

查詢記錄檔使用率 (執行前/後驗證)

DBCC SQLPERF(LOGSPACE);

注意事項與最佳實務

1. 務必先完整備份：縮小操作不可回滾，資料遺失風險極高。
2. 切回 FULL 後立即完整備份：否則後續記錄備份會失敗 (錯誤 4214)。
3. 避免頻繁縮小：反覆增長/縮小造成磁碟碎片與 VLF 碎片化，影響效能。建議預先規劃適當初始大小與自動成長增量 (固定 MB，勿用百分比)。
4. Always On / 複寫環境：切換復原模式可能中斷同步，需協調可用性群組或發行者。

替代方案：定期記錄備份

根治 LDF 暴增的正確做法是排程 BACKUP LOG (如每 15 分鐘)，配合 MAXSIZE 限制檔案上限，而非事後縮小。