您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 縮小交易記錄檔 (LDF) 實戰腳本：從 2.8GB 降到 100MB

編輯：關於SqlServer

SHRINK 無效？手動包裝虛擬記錄檔 (VLF) 強制截斷

現象：資料檔 600MB，記錄檔實際使用 33MB，但檔案大小 2.8 GB。**`DBCC SHRINKFILE` / `BACKUP LOG WITH TRUNCATE_ONLY` 皆無效**，因 VLF 狀態為「作用中」無法釋放。

核心原理

記錄檔內部分為多個 **VLF (Virtual Log Files)**，循環使用。
只有 **尾端連續非作用中 VLF** 才能被 `SHRINK` 釋放。
長時間未備份記錄、大事務、複寫/鏡像/AlwaysOn 同步延遲，會導致作用中 VLF 卡在尾端，檔案無法縮小。

強制縮小腳本 (測試環境驗證後再用於生產)

SET NOCOUNT ON;

DECLARE 
    @LogicalFileName SYSNAME = N'Marias_log',  -- 邏輯檔名，從 sys.database_files 查詢
    @MaxMinutes INT = 10,                      -- 最大嘗試分鐘數
    @NewSizeMB INT = 100;                      -- 目標大小 (MB)

DECLARE @OriginalSize INT;
SELECT @OriginalSize = size 
FROM sys.database_files 
WHERE name = @LogicalFileName;

PRINT 'Original Size = ' + CAST(@OriginalSize * 8 / 1024 AS VARCHAR(10)) + ' MB';

CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL);

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog NVARCHAR(MAX);
SELECT @StartTime = GETDATE(), 
       @TruncLog = N'BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' WITH NO_LOG';  -- 2008 以前用 TRUNCATE_ONLY，新版建議改完整/差異備份後再 SHRINK

-- 先嘗試直接縮小
DBCC SHRINKFILE (@LogicalFileName, @NewSizeMB);
EXEC (@TruncLog);

-- 迴圈寫入/刪除虛擬資料，推進 VLF 循環，將作用中 VLF 從尾端趕走
WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
  AND @OriginalSize = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName)
  AND (@OriginalSize * 8 / 1024) > @NewSizeMB
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 = ' + CAST((SELECT size FROM sys.database_files WHERE name = @LogicalFileName) * 8 / 1024 AS VARCHAR(10)) + ' MB';

DROP TABLE #DummyTrans;
SET NOCOUNT OFF;

⚠️ 生產環境嚴禁事項

禁止操作	後果	正確替代
直接在生產跑上述腳本	產生大量記錄 I/O、膨脹 tempdb、阻塞正常事務、打破備份鏈	先做完整備份 → 再做記錄備份 → 再 SHRINK
使用 `BACKUP LOG ... WITH TRUNCATE_ONLY / NO_LOG`	破壞備份鏈，導致無法時間點還原，2008 後已移除	正規 `BACKUP LOG TO DISK = '...'`
設定 `RECOVERY SIMPLE` 再 SHRINK 再改回 `FULL`	同樣破壞備份鏈，且若期間發生災難無法時間點恢復	維持 `FULL`，定期記錄備份自動截斷 VLF

根治長效機制

定期記錄備份**：每 15-60 分鐘一次，自動標記 VLF 可重用，檔案穩定在合理大小。
監控 VLF 數量/大小**：`DBCC LOGINFO`，VLF > 1000 或單個 > 512 MB 會影響啟動/還原/備份效能，**重建記錄檔** (備份 → 脫機 → 刪除 LDF → 上線 → 重建) 整理 VLF。
避免長事務**：大批次作業分批提交，防止單一事務佔用大量 VLF。
設定合理 `max_log_size` / 自動成長固定 MB**：避免 10% 百分比成長產生碎片 VLF。