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

SQL Server 收縮日誌檔案失敗原因分析與批量收縮優化腳本

編輯：關於SqlServer

問題背景

生產環境部署了每日批量收縮所有使用者資料庫日誌檔的 Agent 作業，近期頻繁報錯：

DBCC SHRINKFILE: Page 1:256 could not be moved because it is a work table page.

導致後續資料庫收縮中斷，日誌檔持續增長，佔用大量磁碟空間。

根因深度分析

1. VLF (Virtual Log Files) 機制

SQL Server 日誌檔內部分為多個 **虛擬日誌檔 (VLF)**。收縮操作只能從**檔案末尾**釋放**未使用的 VLF**。若檔案末尾有作用中 VLF (狀態=2)，收縮將失敗或無效。

2. 驗證當前 VLF 狀態

DBCC LOGINFO(N'YourDatabaseName');

結果欄位解讀：

Status = 0：未使用，可被收縮移除。
Status = 2：作用中，**不可移除**。

DBCC LOGINFO 結果：僅 2 個 VLF，均為作用中

圖示資料庫僅有 2 個 VLF 且皆為作用中，**物理上無法再收縮**，強制執行會報錯。

3. 批量腳本缺陷

原腳本使用游標遍歷資料庫，若其中一個資料庫收縮失敗拋出錯誤，游標循環中斷，導致後續資料庫**完全不執行收縮**，風險放大。

優化方案：智慧收縮腳本 (含 VLF 檢查、錯誤隔離、日誌記錄)

USE [master];
GO

CREATE OR ALTER PROC [dbo].[usp_ShrinkUserDbLogsSmart]
    @TargetFreePercent INT = 20,      -- 目標剩餘空間百分比
    @MinLogSizeMB INT = 100,          -- 最小保留日誌大小 (MB)
    @MaxVlfCount INT = 500            -- VLF 過多警示閾值
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @db SYSNAME, @sql NVARCHAR(MAX), @msg NVARCHAR(MAX);
    DECLARE @totalLogMB DECIMAL(18,2), @freeLogMB DECIMAL(18,2), @usedLogMB DECIMAL(18,2);
    DECLARE @logFileName SYSNAME, @vlfCount INT, @canShrinkMB BIGINT;
    
    -- 結果暫存表
    CREATE TABLE #ShrinkLog (
        DBName SYSNAME, TotalLogMB DECIMAL(18,2), UsedLogMB DECIMAL(18,2),
        FreeLogMB DECIMAL(18,2), TargetShrinkMB BIGINT, VLFCount INT,
        Status VARCHAR(50), Msg NVARCHAR(MAX), RunTime DATETIME DEFAULT GETDATE()
    );

    DECLARE dbCur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT name FROM sys.databases 
        WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
          AND state_desc = 'ONLINE'  -- 排除離線/還原中資料庫
          AND is_read_only = 0;      -- 排除唯讀

    OPEN dbCur;
    FETCH NEXT FROM dbCur INTO @db;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- 1. 取得日誌空間使用情況 & VLF 數量
            SET @sql = N'USE [' + @db + N']; 
                SELECT 
                    @totalOut = SUM(CAST(size AS DECIMAL(18,2))) / 128.0,
                    @usedOut = SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS DECIMAL(18,2))) / 128.0,
                    @fileOut = name,
                    @vlfOut = COUNT(*)
                FROM sys.database_files 
                CROSS APPLY (SELECT COUNT(*) AS cnt FROM sys.dm_db_log_info(DB_ID()) WHERE database_id = DB_ID()) v
                WHERE type = 1;'; -- type=1 為日誌檔
            
            EXEC sp_executesql @sql,
                N'@totalOut DEC(18,2) OUT, @usedOut DEC(18,2) OUT, @fileOut SYSNAME OUT, @vlfOut INT OUT',
                @totalOut = @totalLogMB, @usedOut = @usedLogMB, @fileOut = @logFileName, @vlfOut = @vlfCount;
            
            SET @freeLogMB = @totalLogMB - @usedLogMB;

            -- 2. VLF 過多預警 (影響啟動恢復/備份還原速度)
            IF @vlfCount > @MaxVlfCount
            BEGIN
                INSERT #ShrinkLog VALUES (@db, @totalLogMB, @usedLogMB, @freeLogMB, 0, @vlfCount, 'SKIP_VLF_WARN', 
                    N'VLF 數量 ' + CAST(@vlfCount AS VARCHAR) + N' 超過閾值 ' + CAST(@MaxVlfCount AS VARCHAR) + N'，建議重建日誌檔', DEFAULT);
                FETCH NEXT FROM dbCur INTO @db;
                CONTINUE;
            END

            -- 3. 計算可收縮大小：保留 目標剩餘% 或 最小尺寸
            SET @canShrinkMB = CAST(CASE 
                WHEN @freeLogMB > @totalLogMB * @TargetFreePercent / 100.0 
                THEN @freeLogMB - @totalLogMB * @TargetFreePercent / 100.0
                WHEN @totalLogMB > @MinLogSizeMB 
                THEN @totalLogMB - @MinLogSizeMB
                ELSE 0 END AS BIGINT);

            -- 4. 執行收縮 (僅當可收縮 > 0)
            IF @canShrinkMB > 0
            BEGIN
                SET @sql = N'USE [' + @db + N']; DBCC SHRINKFILE ([' + @logFileName + N'], ' + CAST(@canShrinkMB AS NVARCHAR) + N');';
                EXEC (@sql);
                INSERT #ShrinkLog VALUES (@db, @totalLogMB, @usedLogMB, @freeLogMB, @canShrinkMB, @vlfCount, 'SUCCESS', N'收縮 ' + CAST(@canShrinkMB AS VARCHAR) + N' MB', DEFAULT);
            END
            ELSE
            BEGIN
                INSERT #ShrinkLog VALUES (@db, @totalLogMB, @usedLogMB, @freeLogMB, 0, @vlfCount, 'SKIP_NO_NEED', N'日誌空間充足或已達最小尺寸', DEFAULT);
            END
        END TRY
        BEGIN CATCH
            INSERT #ShrinkLog VALUES (@db, @totalLogMB, @usedLogMB, @freeLogMB, 0, @vlfCount, 'ERROR', 
                ERROR_MESSAGE(), DEFAULT);
            -- 關鍵：捕獲錯誤後繼續下一個資料庫，不中斷整體作業
        END CATCH

        FETCH NEXT FROM dbCur INTO @db;
    END
    CLOSE dbCur; DEALLOCATE dbCur;

    -- 輸出執行報表
    SELECT * FROM #ShrinkLog ORDER BY RunTime;
    DROP TABLE #ShrinkLog;
END;
GO

-- 排程執行範例
-- EXEC msdb.dbo.sp_add_jobstep @job_name=N'Daily_Shrink_Logs', @step_name=N'ShrinkAll', @subsystem=N'TSQL', @command=N'EXEC dbo.usp_ShrinkUserDbLogsSmart;', @on_fail_action=2; -- 繼續下一步

關鍵改進點解析

原腳本痛點	優化方案
無 VLF 檢查，盲目收縮報錯	引入 `sys.dm_db_log_info` (SQL 2017+) 或 `DBCC LOGINFO` 統計 VLF 數量與狀態，僅在有可用 VLF 時執行
單一失敗導致全批次中斷	`TRY...CATCH` 包裹單庫邏輯，錯誤寫入日誌表繼續下一庫
固定收縮到「已用+1」極易失敗	動態計算：保留 `@TargetFreePercent`% 剩餘空間，且不低於 `@MinLogSizeMB`
無執行記錄，事後難追蹤	結果寫入 `#ShrinkLog`，可擴展為持久化記錄表供報表查詢
忽略 VLF 過多隱患	新增 `@MaxVlfCount` 閾值預警，提示重建日誌檔 (備份日誌 -> 收縮到極小 -> 重新擴展至目標大小)

VLF 過多時的根治：重建日誌檔

-- 僅供維護窗口執行，會中斷日誌鏈，需立即執行完整備份！
USE [ProblemDB];
BACKUP LOG [ProblemDB] TO DISK = 'NUL:'; -- 簡單模式或臨時切簡單模式可跳過
DBCC SHRINKFILE ([LogFileName], 10); -- 壓縮到極小 (10MB)
-- 修改檔案大小與成長量 (避免頻繁自動增長產生碎片 VLF)
ALTER DATABASE [ProblemDB] MODIFY FILE (NAME = [LogFileName], SIZE = 4096MB, FILEGROWTH = 512MB);