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

SQL Server 交易記錄檔暴增不縮小？教你用虛擬交易填充法強制縮減 LDF 檔案

編輯：關於SqlServer

問題現象

資料庫實際資料 600 MB，記錄檔初始 33 MB，但實體檔案佔用 2.8 GB。執行 DBCC SHRINKDATABASE、BACKUP LOG ... WITH TRUNCATE_ONLY (舊版) 或 DBCC SHRINKFILE 均無法縮減。原因通常為：

長時間未備份記錄檔（完整復原模式下）
大量交易導致 VLF (虛擬記錄檔) 大量擴展，且未被截斷
複寫/鏡像/可用性群組/異動擷取 (CDC) 等功能延遲截斷

核心解法：虛擬交易填充法

原理：建立臨時表，透過大量 INSERT/DELETE 產生虛擬交易，推進記錄檔截斷點 (Log Truncation Point)，配合 BACKUP LOG ... WITH TRUNCATE_ONLY (SQL 2005/2008) 或切換至 SIMPLE 復原模式 (2008 R2+) 釋放空間，最後 DBCC SHRINKFILE 釋放至目標大小。

完整 T-SQL 腳本 (SQL Server 2005/2008 適用)


SET NOCOUNT ON;

DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT;

-- ===== 參數區：依實際環境修改這三個值 =====
USE [YourDatabaseName];          -- 目標資料庫
SELECT @LogicalFileName = 'YourDatabaseName_log';  -- 邏輯記錄檔名 (sys.database_files 查詢)
SET @MaxMinutes = 10;            -- 最大嘗試分鐘數
SET @NewSize = 100;              -- 目標大小 (MB)
-- ============================================

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

PRINT 'Original log size: ' + CAST(@OriginalSize * 8 / 1024 AS VARCHAR) + ' MB';

-- 建立虛擬交易用臨時表
CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL);

DECLARE @Counter INT, @StartTime DATETIME, @TruncLog NVARCHAR(255);
SELECT @StartTime = GETDATE(),
       @TruncLog = N'BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' 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
    SET @Counter = 0;
    WHILE @Counter < @OriginalSize / 16 AND @Counter < 50000
    BEGIN
        INSERT #DummyTrans VALUES ('Fill Log');
        DELETE #DummyTrans;
        SET @Counter = @Counter + 1;
    END
    EXEC (@TruncLog);
END

-- 最終結果報告
DECLARE @FinalSize INT;
SELECT @FinalSize = size FROM sys.database_files WHERE name = @LogicalFileName;
PRINT 'Final log size: ' + CAST(@FinalSize * 8 / 1024 AS VARCHAR) + ' MB';

DROP TABLE #DummyTrans;
SET NOCOUNT OFF;

關鍵參數說明

參數	說明	取值建議
`@LogicalFileName`	邏輯記錄檔名稱	`SELECT name FROM sys.database_files WHERE type_desc = 'LOG'`
`@MaxMinutes`	最大執行時間上限	10~30 分鐘，視記錄檔大小調整
`@NewSize`	目標縮減大小 (MB)	業務正常產生記錄量的 1.5~2 倍
`#DummyTrans`	單列 8000 bytes，每次 INSERT/DELETE 產生 ~8KB 記錄	迴圈上限 50,000 次防止失控

新版本 (SQL Server 2008 R2 / 2012+) 簡化做法

支援線上切換復原模式，無需 TRUNCATE_ONLY (已移除)：


ALTER DATABASE [YourDB] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (YourDB_log, 100);  -- 目標 MB
ALTER DATABASE [YourDB] SET RECOVERY FULL;
-- 立即執行完整備份啟動新記錄鏈
BACKUP DATABASE [YourDB] TO DISK = '...';

注意：切換至 SIMPLE 會截斷記錄鏈，務必在切回 FULL 後**立即執行完整備份**，否則無法執行記錄備份與時間點還原。