SQL Server 交易記錄檔清理與縮減完整指南：安全操作、風險控制與自動化最佳實務

記錄檔膨脹的根因

• 復原模式為 **完整/大量記錄** 但**長期無記錄備份**，VLF 無法截斷。
• 長時間執行的交易 (Long-running transaction) 阻礙 VLF 重用。
• 異動複寫/鏡像/AlwaysOn 同步延遲導致記錄未硬化/傳送。
• 大量作業 (索引重建、大量匯入) 在完整模式下產生海量記錄。

方法一：標準縮減流程 (安全、可控、生產環境首選)

步驟

1. 執行記錄備份 (完整/大量記錄模式必須) → 截斷非作用中 VLF。
2. DBCC SHRINKFILE 針對記錄檔 指定目標大小 (MB)。
3. 驗證 VLF 數量與大小 避免過度碎片化。

-- 1. 記錄備份
BACKUP LOG [DBName] TO DISK = 'D:\Backup\DBName_LOG_Tail.trn' WITH COMPRESSION, CHECKSUM;

-- 2. 縮減記錄檔至 500 MB (視實際負載調整)
DBCC SHRINKFILE ([DBName]_Log, 500);

-- 3. 檢查 VLF
DBCC LOGINFO ([DBName]);
-- 理想：VLF 數量 < 100，大小均勻 (如 512MB 檔案 → 16 個 32MB VLF)

方法二：簡單復原模式下的自動截斷 (開發/測試/唯讀報表庫)

ALTER DATABASE [DBName] SET RECOVERY SIMPLE;
-- CHECKPOINT 時自動截斷非作用中 VLF
-- 可搭配 DBCC SHRINKFILE 釋放空間
DBCC SHRINKFILE ([DBName]_Log, 100);
-- 生產庫切回完整模式並立即完整備份
ALTER DATABASE [DBName] SET RECOVERY FULL;
BACKUP DATABASE [DBName] TO DISK = '...' WITH INIT, COMPRESSION, CHECKSUM;

⚠️ 生產庫嚴禁長期保持 SIMPLE 模式，將失去時間點還原能力。

方法三：分離/刪除 LDF 重建 (高風險、嚴禁生產、僅限無備份可用的測試庫)

1. 停止服務 → 分離資料庫 → 刪除 `.ldf` → 附加 `.mdf` (SSMS 會自動建立 500KB 新記錄檔)。
2. 風險： 未提交交易遺失、資料庫不一致、破壞複寫/鏡像/AlwaysOn、違反 RPO。

自動化維護：避免記錄檔反覆膨脹

1. 固定記錄檔初始大小與成長增量

-- 一次預配足夠空間，避免頻繁自動成長 (預設 10% 會導致 VLF 碎片化)
ALTER DATABASE [DBName] MODIFY FILE (NAME = [DBName]_Log, SIZE = 4GB, FILEGROWTH = 512MB);

建議：FILEGROWTH 固定 MB (512MB~1GB)，**禁止百分比成長**。

2. 定期記錄備份排程 (Agent 作業)

-- 每 15 分鐘
BACKUP LOG [DBName] TO DISK = 'D:\Backup\DBName_LOG_.trn'
WITH COMPRESSION, CHECKSUM, INIT;
-- 保留 48 小時檔案

3. 監控 VLF 健康度

-- 檢查 VLF 過多 (> 1000) 或過小 (< 1MB) 的資料庫
SELECT name, COUNT(*) AS VLF_Count,
       AVG(size_mb) AS Avg_VLF_MB
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) li
GROUP BY name
HAVING COUNT(*) > 500 OR AVG(size_mb) < 10;

4. 長跑交易告警

SELECT session_id, transaction_id, database_id,
       DATEDIFF(SECOND, start_time, GETDATE()) AS DurationSec
FROM sys.dm_tran_active_transactions
WHERE DATEDIFF(SECOND, start_time, GETDATE()) > 300; -- 5 分鐘

常見誤區與避坑指南

緊急處理：記錄檔佔滿磁碟導致資料庫 Suspect

1. 新增磁碟/清理空間 → 服務自動恢復 (若僅空間不足)。
2. 若已 Suspect：
   `ALTER DATABASE [DBName] SET EMERGENCY;`
   `ALTER DATABASE [DBName] SET SINGLE_USER;`
   `BACKUP LOG [DBName] TO DISK = 'NewDisk:\Tail.trn' WITH NO_TRUNCATE;` (SQL 2005+ 支援)
   `DBCC SHRINKFILE ([DBName]_Log, TargetMB);`
   `ALTER DATABASE [DBName] SET MULTI_USER;`
   `ALTER DATABASE [DBName] SET RECOVERY FULL;` + 立即完整備份。