SQL Server 交易記錄檔截斷與收縮完整操作手冊：從原理到安全實務

前言：記錄檔暴漲是 DBA 最常見的噩夢

當交易記錄檔 (LDF) 佔滿磁碟、導致資料庫掛起、備份失敗時，正確的截斷與收縮操作能化險為夷。**錯誤操作可能導致資料遺失、備份鏈斷裂、資料庫損毀。** 本文依據 Microsoft 官方最佳實務，提供分級操作指南與風險管控。

核心概念：截斷 ≠ 收縮

分級操作指南 (按風險由低到高)

Level 1：正常備份驅動截斷 (零風險、生產環境標準)

-- 完整復原模式：必須定期記錄備份
BACKUP LOG [DatabaseName] TO DISK = 'D:\Backup\DB_Log.trn'
WITH COMPRESSION, CHECKSUM;

-- 簡單復原模式：檢查點自動截斷，無需手動干預
-- 檢查記錄空間使用率
DBCC SQLPERF(LOGSPACE);

原則： 完整復原模式下，**定期記錄備份是唯一正確的截斷方式**。若記錄檔持續增長，檢查：備份作業是否失敗、是否有長時間執行交易、複寫/鏡像/CDC/可用性群組是否阻礙截斷 (`log_reuse_wait_desc`)。

Level 2：緊急手動截斷 (僅限簡單復原模式、非生產環境)

-- 簡單復原模式下強制截斷 (等同 CHECKPOINT)
DBCC SHRINKFILE (N'DatabaseName_Log', 1);  -- 目標大小 1MB，實際以可縮最小為準
-- 或舊版語法 (已棄用)
-- DUMP TRANSACTION DatabaseName WITH NO_LOG;
-- BACKUP LOG DatabaseName WITH NO_LOG;

⚠️ 警告： `WITH NO_LOG` / `WITH TRUNCATE_ONLY` **已在 SQL Server 2008 移除**，使用會破壞完整復原模式下的備份鏈，**嚴禁用於生產環境完整復原模式資料庫**。

Level 3：物理收縮檔案 (謹慎使用、需規劃)

-- 1. 確認可收縮空間
DBCC SQLPERF(LOGSPACE);  -- 查看 Log Space Used %

-- 2. 收縮記錄檔至目標大小 (單位 MB)
DBCC SHRINKFILE (N'DatabaseName_Log', 500);  -- 目標 500 MB

-- 3. 驗證結果
DBCC SQLPERF(LOGSPACE);

最佳實務：

1. 先執行記錄備份 (完整模式) 或 CHECKPOINT (簡單模式) 觸發截斷。
2. 收縮目標設為 **「正常高峰期使用量 + 20% 緩衝」**，而非最小值。
3. **嚴禁排程定期收縮** (會導致檔案反覆增長/收縮、VLF 碎片化、效能抖動)。
4. 收縮後執行 `DBCC LOGINFO` 檢查 VLF 數量與大小分布，理想為 <50 個 VLF、大小均勻。

進階診斷：為何記錄檔不斷增長、無法截斷？

SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'DatabaseName';

資料檔 (MDF/NDF) 收縮：同樣謹慎原則

-- 查看檔案 ID 與可用空間
SELECT file_id, name, size/128.0 AS Size_MB, size/128.0 - FILEPROPERTY(name,'SpaceUsed')/128.0 AS Free_MB
FROM sys.database_files;

-- 收縮資料檔 (指定檔案 ID)
DBCC SHRINKFILE (1, 2000);  -- 目標 2000 MB
-- 或收縮整個資料庫 (不建議，無法精確控制)
-- DBCC SHRINKDATABASE (DatabaseName, 20); -- 目標 20% 剩餘空間

資料檔收縮副作用： 大量頁面移動導致**嚴重索引碎片**，收縮後**必須重建/重組索引** (`ALTER INDEX ... REBUILD/REORGANIZE`)，否則查詢效能大幅下降。

總結：DBA 黃金法則

1. 完整復原模式 + 定期記錄備份 是記錄檔管理的唯一正道。
2. 預先規劃檔案初始大小與自動成長增量 (建議固定 MB，非百分比)，減少自動成長頻率。
3. 監控 `log_reuse_wait_desc` 與 VLF 分布，而非監控檔案大小。
4. 收縮屬於手術刀，而非維生素：僅在檔案異常暴漲 (如一次性大量異動) 事後單次執行，事後重建索引。
5. 文件化操作流程，納入變更管理，嚴禁手工隨意執行 `SHRINKFILE`/`SHRINKDATABASE`。