SQL Server 交易記錄檔暴增縮減實戰：DBCC SHRINKFILE 進階技巧

現象與成因

資料檔 600 MB，記錄檔邏輯 33 MB 卻佔用 2.8 GB 磁碟。常見原因：

• 完整復原模式下未定期備份記錄檔
• 長跑交易阻截記錄截斷
• 異常大量異動（大量匯入、索引重建）
• 複寫/鏡像/可用性群組同步延遲

為何常規 SHRINK 失效？

• DBCC SHRINKDATABASE / SHRINKFILE 僅釋放檔案尾端未使用空間
• 作用中 VLF（虛擬記錄檔）在檔案中間 → 無法縮減
• BACKUP LOG WITH TRUNCATE_ONLY 已移除（SQL 2008+），簡單模式才自動截斷

核心解法：製造虛擬交易推進 VLF 循環

SET NOCOUNT ON
DECLARE @LogicalFileName SYSNAME = 'YourDB_log',  -- 邏輯檔名，非實體路徑
        @MaxMinutes INT = 10,
        @NewSizeMB INT = 100  -- 目標大小 MB

USE YourDB
DECLARE @OriginalSize INT = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName)
PRINT 'Original Size: ' + CAST(@OriginalSize * 8 / 1024 AS VARCHAR) + ' MB'

CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL)
DECLARE @StartTime DATETIME = GETDATE(),
        @TruncLog NVARCHAR(MAX) = N'BACKUP LOG [' + DB_NAME() + N'] WITH NO_LOG'  -- 2005/2008 相容

-- 簡單模式直接 CHECKPOINT；完整模式需備份記錄
IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
    SET @TruncLog = N'CHECKPOINT'
ELSE
    SET @TruncLog = N'BACKUP LOG [' + DB_NAME() + N'] TO DISK = ''NUL:'' WITH NOFORMAT, INIT, NAME = ''TruncateLog'''  -- 開發環境可用 NUL 裝置

DBCC SHRINKFILE (@LogicalFileName, @NewSizeMB)
EXEC (@TruncLog)

WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
  AND @OriginalSize = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName)
  AND (@OriginalSize * 8 / 1024) > @NewSizeMB
BEGIN
    DECLARE @Counter INT = 0
    WHILE @Counter < @OriginalSize / 16 AND @Counter < 50000
    BEGIN
        INSERT #DummyTrans VALUES ('Fill Log')
        DELETE #DummyTrans
        SET @Counter += 1
    END
    EXEC (@TruncLog)
END

PRINT 'Final Size: ' + CAST((SELECT size FROM sys.database_files WHERE name = @LogicalFileName) * 8 / 1024 AS VARCHAR) + ' MB'
DROP TABLE #DummyTrans
SET NOCOUNT OFF

關鍵機制

1. 插入/刪除大量資料 → 填滿記錄檔 → 強制 VLF 循環
2. CHECKPOINT（簡單模式）或 BACKUP LOG（完整模式）截斷非作用 VLF
3. SHRINKFILE 釋放檔案尾端空間
4. 迴圈直到達標或逾時

生產環境安全縮減流程

1. 完整備份 資料庫 + 記錄檔
2. 確認復原模式：完整模式需先 記錄備份 截斷
3. 檢查 VLF 分佈：DBCC LOGINFO，避免過多小 VLF 造成效能問題
4. 設定合理 初始大小與自動成長（固定 MB，非百分比）
5. 排程定期記錄備份，防止再次暴增

預防勝於治療

緊急情況：記錄檔撐滿磁碟

1. 切換簡單模式：ALTER DATABASE YourDB SET RECOVERY SIMPLE
2. CHECKPOINT 截斷
3. SHRINKFILE 至合理大小
4. 切回完整模式並立即完整備份（啟動新記錄鏈）

警告：切換復原模式會中斷記錄鏈，必須完整備份才能恢復時間點還原能力。