SQL Server 交易記錄檔暴增縮減實戰：DBCC SHRINKFILE 配合虛擬交易填充法

現象

資料檔 600 MB，記錄檔實際 33 MB，但記錄檔佔用空間高達 2.8 GB。DBCC SHRINKDATABASE、BACKUP LOG ... TRUNCATE_ONLY 均無法縮減。此為已知行為：記錄檔虛擬記錄檔 (VLF) 未釋放，需手動填充循環強制截斷。

完整 T-SQL 縮減腳本

SET NOCOUNT ON
DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT

USE YourDatabase          -- 目標資料庫名
SELECT @LogicalFileName = 'YourDatabase_log',  -- 邏輯記錄檔名 (sys.database_files 查詢)
       @MaxMinutes = 10,                       -- 最大嘗試分鐘數
       @NewSize = 100                          -- 目標大小 (MB)

DECLARE @OriginalSize INT
SELECT @OriginalSize = size FROM sys.database_files WHERE name = @LogicalFileName
PRINT 'Original Size of ' + DB_NAME() + ' LOG is ' + CAST(@OriginalSize AS varchar) + ' 8K pages or ' + CAST(@OriginalSize*8/1024 AS varchar) + 'MB'

CREATE TABLE DummyTrans (DummyColumn char(8000) NOT NULL)

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + DB_NAME() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)

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

PRINT 'Final Size of ' + DB_NAME() + ' LOG is ' + CAST(size AS varchar) + ' 8K pages or ' + CAST(size*8/1024 AS varchar) + 'MB'
FROM sys.database_files WHERE name = @LogicalFileName

DROP TABLE DummyTrans
SET NOCOUNT OFF

原理說明

1. DBCC SHRINKFILE 嘗試縮減至目標大小，但受限於作用中 VLF 位置可能無法立即生效。
2. BACKUP LOG ... WITH TRUNCATE_ONLY (SQL 2005 以前) 或 BACKUP LOG ... WITH NO_LOG 截斷非作用中部分。
3. 迴圈中大量 INSERT/DELETE 產生虛擬交易，推進記錄檔寫入指標，使舊 VLF 變為非作用中，再次截斷即可釋放空間。
4. @MaxMinutes 限制最長執行時間，避免無限循環。

注意事項

• SQL Server 2008+ 已移除 TRUNCATE_ONLY/NO_LOG，改用 簡單復原模式 切換後執行 DBCC SHRINKFILE，或定期 BACKUP LOG 至磁碟維持記錄檔大小。
• 生產環境執行前請完整備份，並在維護時段進行。
• 記錄檔過大通常源於：長時間交易、大量作業未備份記錄、復原模式設定不當。根治需調整備份策略與交易設計。