SQL Server 交易記錄檔管理全攻略：清除、壓縮、刪除與最佳實務

前言

交易記錄檔 (.ldf) 失控增長是 DBA 常見痛點。本文彙整記錄檔清除、壓縮、刪除的實務操作與風險控制。

一、清除非作用中記錄：BACKUP LOG ... WITH NO_LOG

-- 截斷非作用中虛擬記錄檔 (VLF)，不縮減實體檔案大小
BACKUP LOG database_name WITH NO_LOG;

-- 接著壓縮資料庫釋放空間
DBCC SHRINKDATABASE (database_name, target_percent);
-- 或針對記錄檔
DBCC SHRINKFILE (log_file_logical_name, target_size_MB);

關鍵點：

• NO_LOG 僅標記非作用中 VLF 可覆寫，不減小實體檔案
• 必須接著 SHRINKFILE 才能釋放磁碟空間
• 若資料庫復原模式為「完整」，建議改用 BACKUP LOG TO DISK 正規備份後再壓縮
• 執行後無法以最近完整備份做時間點還原，建議立即做完整備份建立新基準

二、變更復原模式為「簡單」

ALTER DATABASE database_name SET RECOVERY SIMPLE;

• 自動截斷非作用中記錄，記錄檔維持極小
• 代價：不支援時間點還原，僅能還原至最近完整/差異備份
• 適用：測試環境、資料可重建、唯讀資料倉儲

三、離線刪除記錄檔並重建 (高風險，慎用)

1. 企業管理員 → 右鍵資料庫 → 所有工作 → 卸離資料庫
2. 檔案系統刪除 .ldf
3. 企業管理員 → 右鍵資料庫 → 所有工作 → 附加資料庫 → 僅選 .mdf
4. SQL Server 自動建立新記錄檔 (1 MB 起始)

風險：未認可交易遺失、資料庫可能進入置疑模式、破壞交易一致性。僅限開發/測試環境或極端災難救援。

四、資料庫與記錄檔壓縮完整指令

DBCC SHRINKDATABASE (整庫壓縮)

DBCC SHRINKDATABASE (database_name [, target_percent]
    [, {NOTRUNCATE | TRUNCATEONLY}]);

• target_percent：壓縮後保留未使用空間百分比
• NOTRUNCATE：釋放空間保留在資料庫檔案內，不還給 OS
• TRUNCATEONLY：僅釋放檔案末端未使用空間，不移動資料頁 (最快、最安全)

DBCC SHRINKFILE (單一檔案壓縮)

DBCC SHRINKFILE (file_id_or_logical_name [, target_size_MB]
    [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]);

• 指定 target_size_MB 直接縮減至指定大小
• EMPTYFILE：將資料移至同檔案群組其他檔案，準備移除檔案

五、自動壓縮設定 (生產環境不建議)

企業管理員 → 資料庫屬性 → 選項 → 「自動壓縮」/「自動壓縮排程」。

缺點：索引碎片化嚴重、CPU/IO 突發、不可預測執行時間。建議改用維護計畫排程手動壓縮。

六、最佳實務總結

1. 生產環境採用完整復原模式 + 定期記錄備份 (每 15-60 分鐘) + 週期完整備份
2. 記錄檔成長設定：固定 MB 成長 (如 512 MB)，避免百分比成長導致 VLF 碎片化
3. 定期監控 DBCC SQLPERF(LOGSPACE) 觀察使用率
4. 壓縮操作安排在離峰時段，並配合索引重建/重組維護
5. 絕不在生產環境直接刪除 .ldf 或使用 NO_LOG 繞過備份鏈