核心原則
- 記錄檔 (LDF) 無限增長 通常因復原模式為 **FULL/BULK_LOGGED** 且 **長期未備份交易記錄**。
- 收縮 (SHRINK) 非常態維護：頻繁收縮導致索引碎片、VLF 碎片化、效能抖動。應修正根因 (定期記錄備份、調整復原模式)。
- 截斷 ≠ 收縮：截斷釋放邏輯空間 (VLF 狀態變為可重用)，收縮歸還物理檔案空間給 OS。
版本別操作指令
SQL Server 2000
-- 截斷記錄 (舊語法)
DUMP TRANSACTION [dbname] WITH NO_LOG;
BACKUP LOG [dbname] WITH NO_LOG;
-- 收縮資料庫
DBCC SHRINKDATABASE(dbname);
SQL Server 2005
BACKUP LOG [dbname] WITH NO_LOG; -- 2005 仍支援
-- 或
BACKUP LOG [dbname] WITH TRUNCATE_ONLY;
DBCC SHRINKFILE (2); -- 檔案 ID 2 通常為記錄檔
SQL Server 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017 / 2019 (標準流程)
-- 1. 切換至簡單復原模式 (自動截斷非作用中 VLF)
ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
-- 2. 收縮記錄檔至目標大小 (MB)，TRUNCATEONLY 僅釋放檔案尾端未使用空間，不移動資料頁
USE [dbname];
DBCC SHRINKFILE (N'dbname_log', 1024, TRUNCATEONLY); -- 目標 1 GB，視需求調整
GO
-- 3. 切回完整復原模式 (生產環境必須)
ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT;
GO
-- 4. 立即執行完整備份 + 記錄備份，啟動新的記錄鏈
BACKUP DATABASE [dbname] TO DISK = 'path\dbname_full.bak' WITH INIT, COMPRESSION;
BACKUP LOG [dbname] TO DISK = 'path\dbname_log.trn' WITH INIT, COMPRESSION;
關鍵細節
- 邏輯檔案名稱：執行 `SELECT name FROM sys.database_files WHERE type_desc = 'LOG';` 確認邏輯名 (如 `dbname_log`)。
- TRUNCATEONLY：僅釋放檔案末端未配置空間，**不移動資料頁**，避免索引碎片，速度最快。
- 目標大小：建議保留至少 **25-50% 預留空間** 或預估單次最大交易記錄量，避免頻繁自動增長。
- VLF 管理：記錄檔增長以 **VLF (Virtual Log File)** 為單位。過多微小 VLF (< 1000) 或過少巨大 VLF 皆影響啟動恢復/備份/鏡像效能。建議預先規劃記錄檔初始大小與自動增長固定值 (如 512 MB/1 GB)。
替代方案：不收縮的「偽收縮」 (極端情況)
-- 僅截斷記錄，不歸還磁碟空間 (瞬時完成)
ALTER DATABASE [dbname] SET RECOVERY SIMPLE;
CHECKPOINT; -- 強制截斷
ALTER DATABASE [dbname] SET RECOVERY FULL;
-- 隨後立即完整備份 + 記錄備份
監控與告警
- 記錄檔使用率：`DBCC SQLPERF(LOGSPACE);` 或查詢 `sys.dm_db_log_space_usage` (2012+)。
- VLF 數量/大小分布：`DBCC LOGINFO;`
- 告警閾值：記錄檔使用率 > 80% 持續 15 分鐘、VLF 數 > 1000、自動增長頻率 > 1 次/小時。
