SQL Server 交易記錄檔暴增縮減實戰：SHRINKFILE 失效根因與分步縮減腳本

常見痛點：資料檔 600 MB，記錄檔實際使用 33 MB，**配置大小卻達 2.8 GB**。`DBCC SHRINKFILE`、`BACKUP LOG ... TRUNCATE_ONLY` (已移除) 均無效。根因：**虛擬記錄檔 (VLF) 作用中部分位於檔案末端**，導致縮減無法釋放空間。

根因深度解析

• 記錄檔內部分為多個 **VLF (Virtual Log Files)**，循環使用。
• **作用中 VLF (含未截斷交易、複寫/CDC/鏡像/AlwaysOn 未讀取、長時間執行交易)** 若位於檔案物理末端，`SHRINKFILE` 無法移動它，**縮減停在該 VLF 起始位置**。
• 完整復原模式下，**未執行記錄備份** 導致所有 VLF 維持作用中，檔案持續成長至上限或磁碟滿。

分步縮減腳本 (生產環境驗證可用)

SET NOCOUNT ON;

DECLARE @LogicalFileName SYSNAME = N'MyDB_Log';  -- 邏輯檔案名 (sys.master_files.name)
DECLARE @TargetSizeMB   INT        = 512;        -- 目標大小 (MB)
DECLARE @MaxMinutes     INT        = 10;         -- 最大嘗試分鐘數

-- 1. 確認目前大小
DECLARE @CurrentSizeMB INT;
SELECT @CurrentSizeMB = size * 8 / 1024 
FROM sys.master_files 
WHERE database_id = DB_ID() AND name = @LogicalFileName;

PRINT 'Current Log Size: ' + CAST(@CurrentSizeMB AS VARCHAR(10)) + ' MB';
IF @CurrentSizeMB <= @TargetSizeMB
BEGIN
    PRINT 'Already <= target size. No action needed.';
    RETURN;
END

-- 2. 完整復原模式：先執行記錄備份 (截斷非作用中 VLF)
IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'FULL'
BEGIN
    DECLARE @BackupPath NVARCHAR(500) = N'D:\Backup\' + DB_NAME() + '_Log_' + REPLACE(CONVERT(VARCHAR(19), GETDATE(), 126), ':', '') + '.trn';
    BACKUP LOG @LogicalFileName TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM;
    PRINT 'Log backup taken: ' + @BackupPath;
END

-- 3. 嘗試直接縮減
DBCC SHRINKFILE (@LogicalFileName, @TargetSizeMB);

-- 4. 若仍過大，迴圈製造虛擬交易推進 VLF 循環 (最後手段)
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @Counter INT, @DummyTableName SYSNAME = N'##ShrinkLogDummy_' + CAST(@@SPID AS VARCHAR(10));

CREATE TABLE #Dummy (col CHAR(8000) NOT NULL);  -- 每列 ~8KB，快速填滿 VLF

WHILE (SELECT size * 8 / 1024 FROM sys.master_files WHERE database_id = DB_ID() AND name = @LogicalFileName) > @TargetSizeMB
  AND DATEDIFF(MI, @StartTime, GETDATE()) < @MaxMinutes
BEGIN
    -- 批次寫入/刪除推進記錄檔指標
    SET @Counter = 0;
    WHILE @Counter < 1000
    BEGIN
        INSERT #Dummy VALUES (REPLICATE('X', 8000));
        DELETE #Dummy;
        SET @Counter += 1;
    END
    -- 簡單復原模式可直接 TRUNCATE；完整模式需再次記錄備份
    IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'SIMPLE'
        DBCC SHRINKFILE (@LogicalFileName, @TargetSizeMB);
    ELSE
    BEGIN
        BACKUP LOG @LogicalFileName TO DISK = @BackupPath WITH COMPRESSION, CHECKSUM;
        DBCC SHRINKFILE (@LogicalFileName, @TargetSizeMB);
    END
END

DROP TABLE #Dummy;

-- 5. 最終確認
DECLARE @FinalSizeMB INT;
SELECT @FinalSizeMB = size * 8 / 1024 
FROM sys.master_files 
WHERE database_id = DB_ID() AND name = @LogicalFileName;
PRINT 'Final Log Size: ' + CAST(@FinalSizeMB AS VARCHAR(10)) + ' MB';

SET NOCOUNT OFF;

關鍵技術點說明

預防措施：杜絕記錄檔暴增

1. 完整復原模式 + 定期記錄備份：每 15-60 分鐘 (視 RPO) 執行 `BACKUP LOG`，自動截斷 VLF。
2. 監控 VLF 數量與大小：`DBCC LOGINFO`，VLF 過多 (>1000) 或過大 (>512 MB) 影響啟動/還原/備份速度，**預先規劃記錄檔初始大小/成長量 (固定 MB，非百分比)**。
3. 避免長時間交易：大量刪除/更新分批提交，防止單一交易鎖定 VLF 導致成長。
4. 複寫/CDC/鏡像/AlwaysOn 監控：`log_reuse_wait_desc` 顯示 `REPLICATION`/`AVAILABILITY_REPLICA` 等，及時處理延遲。
5. 簡單復原模式適用場景：可接受資料遺失 (RPO = 完整備份間隔) 的開發/測試/資料倉儲暫存庫，自動截斷 Checkpoint 時 VLF。

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

1. 新增一個記錄檔 (`.ldf`) 於**不同磁碟** (`ALTER DATABASE ADD LOG FILE`)，暫時緩解。
2. 執行上述縮減腳本 (簡單模式最快)。
3. 若無法縮減，檢查 `log_reuse_wait_desc`，針對性解除阻塞 (如結束長交易、重啟複寫代理、執行記錄備份)。