縮減 SQL Server 記錄檔的實戰腳本：填滿記錄強制截斷法

問題現象

資料庫實際資料僅 600MB，記錄檔卻佔用 2.8GB，且 DBCC SHRINKFILE、BACKUP LOG ... TRUNCATE_ONLY 均無法釋放空間。這通常是因記錄檔內有大量未截斷的虛擬記錄檔 (VLF) 導致。

核心腳本（適用 SQL Server 2000/2005/2008+）

SET NOCOUNT ON;
DECLARE @LogicalFileName SYSNAME,
        @MaxMinutes INT,
        @NewSize INT;

USE YourDatabase;          -- 修改：目標資料庫名
SELECT @LogicalFileName = 'YourDatabase_log',  -- 修改：邏輯記錄檔名（sp_helpfile 查看）
       @MaxMinutes = 10,                      -- 最大等待分鐘數
       @NewSize = 100;                        -- 修改：目標大小 (MB)

DECLARE @OriginalSize INT;
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName;
PRINT 'Original Size: ' + 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 sysfiles 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

SELECT 'Final Size: ' + CAST(size * 8 / 1024 AS VARCHAR) + ' MB'
FROM sysfiles WHERE name = @LogicalFileName;

DROP TABLE DummyTrans;

原理解析

1. 先嘗試 SHRINKFILE + TRUNCATE_ONLY。
2. 若記錄檔未縮小，透過大量 INSERT/DELETE 產生交易，填滿記錄檔，強制記錄檔捲動，使非作用 VLF 變為可截斷狀態。
3. 循環中每次寫入約 @OriginalSize/16 筆 8KB 記錄，足以推進 VLF。
4. 設定 @MaxMinutes 防止無限循環。

使用建議

• 執行前請**完整備份資料庫**，確保可復原。
• 確認 @LogicalFileName 正確（執行 sp_helpfile 查看）。
• 目標大小 @NewSize 建議設為略大於實際使用量（如 1.2 倍）。
• SQL Server 2008+ 建議改用 BACKUP LOG ... TO DISK 正規記錄備份，而非 TRUNCATE_ONLY。

根治方案

定期記錄備份（如每 15 分鐘）+ 監控記錄檔使用率，避免記錄檔失控增長。