SQL Server 2005/2008 鏡像環境下 LDF 檔案暴增的解決策略

現象與誤區

資料庫鏡像 是高可用性的熱門選擇，但許多 DBA 發現主資料庫的交易記錄檔（LDF）持續增長，甚至填滿磁碟。常見的 DUMP TRANSACTION ... WITH NO_LOG 或 BACKUP LOG ... WITH NO_LOG 在鏡像環境下完全無效，因為鏡像機制要求記錄必須保留以供鏡像夥伴同步。

核心原則：鏡像下唯有「定期備份記錄」才能截斷非作用中 VLF

記錄檔內部由多個虛擬記錄檔（VLF）組成。只有當記錄備份完成，且鏡像夥伴已同步確認，對應的 VLF 才會標記為可重複使用。因此：

• 簡單復原模式不支援鏡像，必須使用 完整復原模式。
• 必須建立 定期記錄備份作業（如每 15-60 分鐘）。

三步驟緩解與長效治理

1. 新增次要記錄檔（緊急止血）

ALTER DATABASE [DB_Name] ADD LOG FILE (
    NAME = N'DB_Name_Log2',
    FILENAME = N'X:\MSSQL\Log\DB_Name_Log2.ldf',
    SIZE = 1024MB,
    MAXSIZE = 4096MB,
    FILEGROWTH = 512MB
);

將新記錄檔放在不同磁碟，避免單一檔案撐爆單一磁碟區。

2. 建立高頻率記錄備份作業（核心解法）

-- 範例：每 15 分鐘備份一次記錄
BACKUP LOG [DB_Name] TO DISK = N'X:\Backup\DB_Name_Log_$(datetime).trn'
WITH COMPRESSION, CHECKSUM, FORMAT;

• 使用 SQL Server Agent 排程或 Maintenance Plan。
• 備份目標建議放在與資料/記錄檔不同的儲存裝置。
• 啟用 COMPRESSION 減少備份體積與 I/O。

3. 監控與收縮（輔助手段）

-- 查看各資料庫記錄空間使用率
DBCC SQLPERF(LOGSPACE);

-- 查看記錄無法重複使用的原因
SELECT name, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'DB_Name';

常見 log_reuse_wait_desc 值：

僅在確認 log_reuse_wait_desc = NOTHING 且記錄檔確有大量剩餘空間時，才考慮 DBCC SHRINKFILE (Log_Name, 目標大小_MB)。頻繁收縮會造成 VLF 碎片化，反而傷害效能。

實戰檢查清單

1. 確認資料庫為完整復原模式。
2. 驗證記錄備份作業正常執行且無錯誤。
3. 檢查鏡像狀態為 SYNCHRONIZED，無 SUSPENDED 或 DISCONNECTED。
4. 監控磁碟剩餘空間，設定警示閾值（如 < 20%）。
5. 定期執行 DBCC LOGINFO 觀察 VLF 數量與大小，避免過多小 VLF 影響啟動恢復速度。

結語

鏡像環境下 LDF 暴增本質上是「記錄產生速度 > 記錄截斷速度」。唯有落實高頻率記錄備份、保障鏡像同步暢通、並輔以多記錄檔分散 I/O，才能從根本上控制檔案大小，確保系統穩定運行。