交易記錄 (LDF) 無限增長是 SQL Server 維運常見痛點。記錄檔暴增不僅佔用磁碟、導致備份時間暴長、還原耗時，嚴重時甚至填滿磁碟導致資料庫掛起。本文針對 SQL Server 2000 至 2022 各版本，提供標準化清空縮減流程、恢復模式切換策略、自動化維護腳本與預防措施。
| 恢復模式 | 記錄行為 | 適用場景 | 截斷機制 |
|---|---|---|---|
| SIMPLE (簡單) | 最小記錄，Checkpoint 時自動截斷非作用中 VLF | 開發/測試/資料倉儲/可接受資料遺失 | 自動 (Checkpoint) |
| FULL (完整) | 完整記錄所有交易，支援時間點還原 | 生產/OLTP/高可用性 | **僅在記錄備份後** |
| BULK_LOGGED | 大量作業最小記錄，其他完整記錄 | 大量匯入/索引重建期間暫時切換 | 記錄備份後 |
⚠️ **FULL 模式下若無定期記錄備份，記錄檔會無限增長直到磁碟滿。**
-- 1. 截斷記錄 (無記錄備份)
DUMP TRANSACTION [DBName] WITH NO_LOG;
-- 或
BACKUP LOG [DBName] WITH NO_LOG;
-- 2. 縮減資料庫 (含記錄檔)
DBCC SHRINKDATABASE ([DBName]);
-- 或指定記錄檔
DBCC SHRINKFILE ([DBName_Log], 10); -- 目標 10 MB
DUMP/BACKUP ... WITH NO_LOG 在 2005 已移除，2008 完全不支援。
-- 方法一：T-SQL + 手動縮減
BACKUP LOG [DBName] WITH NO_LOG; -- 2005 仍支援但不建議
-- 或
BACKUP LOG [DBName] TO DISK = 'NUL:'; -- 導向空裝置 (不建議生產)
-- 正規流程：
-- 1. 確認記錄備份正常運行
-- 2. 手動縮減 (SSMS: 任務 → 縮減 → 檔案 → 記錄 → 重組頁面再釋放)
DBCC SHRINKFILE ([DBName_Log], 100); -- 目標大小 MB
核心原則：FULL 模式下不可直接截斷記錄。必須暫時切換至 SIMPLE 模式 → 縮減 → 切回 FULL → **立即執行完整備份 + 記錄備份** 重新啟動記錄鏈。
DECLARE @DBName SYSNAME = N'YourDB';
DECLARE @TargetLogSizeMB INT = 500; -- 目標記錄檔大小
DECLARE @LogFileName SYSNAME;
-- 取得邏輯記錄檔名稱
SELECT @LogFileName = name FROM sys.master_files
WHERE database_id = DB_ID(@DBName) AND type_desc = 'LOG';
BEGIN TRY
-- 1. 切換至 SIMPLE (截斷非作用中 VLF)
EXEC (N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY SIMPLE WITH NO_WAIT');
-- 2. 縮減記錄檔 (TRUNCATEONLY 僅釋放未使用空間，不移動資料頁，極快)
EXEC (N'USE [' + @DBName + N']; DBCC SHRINKFILE (N''' + @LogFileName + N''', ' + CAST(@TargetLogSizeMB AS NVARCHAR(10)) + N', TRUNCATEONLY)');
-- 3. 切回 FULL
EXEC (N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY FULL WITH NO_WAIT');
-- 4. 關鍵：立即建立完整備份 (啟動新記錄鏈)
-- BACKUP DATABASE [@DBName] TO DISK = '...' WITH COMPRESSION, CHECKSUM;
-- 5. 立即建立記錄備份
-- BACKUP LOG [@DBName] TO DISK = '...' WITH COMPRESSION, CHECKSUM;
PRINT N'記錄檔縮減完成，請立即執行完整備份與記錄備份！';
END TRY
BEGIN CATCH
-- 發生錯誤嘗試切回 FULL
EXEC (N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY FULL WITH NO_WAIT');
THROW;
END CATCH
| 風險 | 後果 | 緩解措施 |
|---|---|---|
| 切回 FULL 後未立即完整備份 | 記錄鏈中斷，後續記錄備份失敗，無法時間點還原 | 腳本強制結合備份步驟，或設定 Agent 作業鏈結 |
| 頻繁縮減 (每日/每週) | 記錄檔反覆增長/縮減 → 檔案碎片化、VLF 過多 → 啟動/還原/備份變慢 | **找出增長根因** (長交易、大批次、未備份)，修正根因而非症狀 |
| TRUNCATEONLY 後仍巨大 | 作用中 VLF 未釋放 (長交易、複寫、CDC、可用性群組) | DBCC OPENTRAN、sys.dm_tran_database_transactions 排查 |
| 目標大小設太小 | 下次交易立即觸發自動增長 → 效能抖動、VLF 碎片 | 設定合理大小 (如預估峰值 1.2 倍)，啟用瞬間檔案初始化 |
-- 檢視 VLF 數量與狀態
DBCC LOGINFO (YourDB);
-- 狀態 0 = 非作用中 (可覆寫/截斷), 2 = 作用中
-- VLF 過多 (>1000) 會嚴重影響啟動、還原、備份、鏡像/AG 同步速度
**VLF 最佳實務** (SQL 2014+ 自動優化，舊版需手動)：
SE_MANAGE_VOLUME_NAME (執行磁碟區維護工作)DBCC LOGINFO VLF 數量-- 每日檢查記錄檔使用率 > 80% 且 FULL 模式時發出警示
EXEC msdb.dbo.sp_add_alert
@name = N'Log File Usage High',
@message_id = 0,
@severity = 0,
@enabled = 1,
@delay_between_responses = 3600,
@include_event_description_in = 1,
@job_id = '...'; -- 關聯通知作業
-- 定期記錄備份作業 (每 15-60 分鐘)
BACKUP LOG [DBName] TO DISK = 'Path\DBName_Log_%DATE%.trn'
WITH COMPRESSION, CHECKSUM, FORMAT, INIT;
log_reuse_wait_desc-- 診斷記錄無法重用原因
SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'YourDB';
-- 常見值: LOG_BACKUP, ACTIVE_TRANSACTION, REPLICATION, AVAILABILITY_REPLICA
log_reuse_wait_desc → 解決根因 (補記錄備份/結束長交易/修復複寫)DBCC SHRINKFILE (..., TRUNCATEONLY) → FULL → **立即完整備份 + 記錄備份**交易記錄管理的核心不是「如何縮減」，而是**建立正確的備份策略、監控機制與容量規劃**，讓記錄檔在穩定範圍內運行。縮減是手術刀，備份才是免疫系統。
