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

SQL Server 交易記錄檔縮小完整指南：從原理到實戰腳本

編輯：關於SqlServer

問題現象

資料庫本體 600MB，記錄檔邏輯大小 33MB，但實體檔案佔用 2.8GB。嘗試 `SHRINK DATABASE`、`TRUNCATE LOG` 均無效。核心原因：**邏輯記錄截斷 (Truncate) 與物理檔案縮小是兩碼事**，且記錄檔內部可能因長時間未備份、異常關機、大量交易導致虛擬記錄檔 (VLF) 狀態為「作用中」無法截斷。

核心原理：VLF 與截斷機制

記錄檔內部分為多個 **虛擬記錄檔 (VLF)**。
只有 **尾端連續的非作用中 VLF** 才能被截斷並釋放空間給作業系統。
若尾端 VLF 為「作用中」(含未提交交易、未備份的大量記錄、複寫/鏡像/CDC 待傳送記錄)，`DBCC SHRINKFILE` 只能縮小至該 VLF 起始位置。

標準解決流程 (生產環境建議)

完整備份資料庫 (含記錄檔) 確保可復原點。
交易記錄備份 (`BACKUP LOG dbname TO DISK = '...'`) — 將非作用中 VLF 標記為可覆寫，這是最關鍵步驟。
執行縮小 (`DBCC SHRINKFILE (logical_log_name, target_size_MB)`)。
若仍無法縮小，檢查 `sys.databases.log_reuse_wait_desc` 確認阻塞原因 (如 `REPLICATION`, `LOG_BACKUP`, `ACTIVE_TRANSACTION`)。

緊急/特殊情境腳本：虛擬交易強制包裝 (謹慎使用)

⚠️ 僅限非生產環境或無法備份的極端情況。原理：寫入大量虛擬交易填滿記錄檔，迫使作用中 VLF 循環至開頭，再截斷尾端。

USE [YourDatabase]; -- 修改為目標資料庫
GO

DECLARE @LogicalFileName sysname = N'YourDatabase_log'; -- 修改為邏輯記錄檔名 (sp_helpfile 查詢)
DECLARE @TargetSizeMB INT = 100; -- 目標大小 (MB)
DECLARE @MaxMinutes INT = 10; -- 最大嘗試分鐘數

-- 1. 記錄原始大小
DECLARE @OriginalSize INT = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName);
PRINT '原始大小: ' + CAST(@OriginalSize * 8 / 1024 AS VARCHAR) + ' MB';

-- 2. 嘗試直接縮小 (若 VLF 允許)
DBCC SHRINKFILE (@LogicalFileName, @TargetSizeMB);

-- 3. 若未達目標，執行虛擬交易包裝迴圈
IF (SELECT size FROM sys.database_files WHERE name = @LogicalFileName) * 8 / 1024 > @TargetSizeMB
BEGIN
    CREATE TABLE #DummyTrans (DummyColumn CHAR(8000) NOT NULL);
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @Counter INT;
    DECLARE @TruncLog NVARCHAR(MAX) = N'BACKUP LOG [' + DB_NAME() + N'] WITH TRUNCATE_ONLY'; -- 簡單復原模式專用

    WHILE @MaxMinutes > DATEDIFF(MI, @StartTime, GETDATE())
          AND (SELECT size FROM sys.database_files WHERE name = @LogicalFileName) * 8 / 1024 > @TargetSizeMB
    BEGIN
        SET @Counter = 0;
        WHILE @Counter < 50000 -- 安全上限
        BEGIN
            INSERT #DummyTrans VALUES ('Fill Log');
            DELETE #DummyTrans;
            SET @Counter += 1;
        END
        EXEC (@TruncLog); -- 截斷非作用中部分
        DBCC SHRINKFILE (@LogicalFileName, @TargetSizeMB); -- 嘗試縮小
    END
    DROP TABLE #DummyTrans;
END

-- 4. 最終結果
DECLARE @FinalSizeMB INT = (SELECT size FROM sys.database_files WHERE name = @LogicalFileName) * 8 / 1024;
PRINT '最終大小: ' + CAST(@FinalSizeMB AS VARCHAR) + ' MB';
GO

關鍵參數說明

`@LogicalFileName`：執行 `sp_helpfile` 或查詢 `sys.database_files` 獲取 `name` 欄位 (通常為 `資料庫名_log`)。
`@TargetSizeMB`：目標大小，**不可小於邏輯使用空間**，建議預留 20-50% 成長空間。
`BACKUP LOG ... WITH TRUNCATE_ONLY`：僅適用 **簡單復原模式**。完整/大量記錄復原模式**必須改為正規 `BACKUP LOG TO DISK`**，否則破壞記錄鏈導致無法時間點還原。