萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 日志文件無法收縮的原因分析與解決方案

SQL Server 日志文件無法收縮的原因分析與解決方案

近期伺服器執行的收縮日志文件作業頻繁報錯,筆者整理了解決方案並分享至腳本之家平台,供大家參考學習。

問題現象

執行批量收縮日志腳本時,作業報錯中斷。錯誤截圖如下:

原始收縮腳本

以下是筆者使用的批量收縮日志預存程序:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[ShrinkUser_DATABASESLogFile] Script Date: 01/05/2016 09:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ShrinkUser_DATABASESLogFile] AS
BEGIN
    DECLARE @DBNAME NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)
    -- 臨時表保存數據
    CREATE TABLE #DataBaseServerData (
        ID INT IDENTITY(1, 1),
        DBNAME NVARCHAR(MAX),
        Log_Total_MB DECIMAL(18, 1) NOT NULL,
        Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL
    )
    -- 游標遍歷數據庫
    DECLARE @itemCur CURSOR
    SET @itemCur = CURSOR FOR
    SELECT name FROM SYS.[databases]
    WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution')
      AND state = 0
    OPEN @itemCur
    FETCH NEXT FROM @itemCur INTO @DBNAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'USE [' + @DBNAME + '];' + CHAR(10) +
            ' DECLARE @TotalLogSpace DECIMAL(18, 1)' +
            ' DECLARE @FreeLogSpace DECIMAL(18, 1)' +
            ' DECLARE @filename NVARCHAR(MAX)' +
            ' DECLARE @CanshrinkSize BIGINT' +
            ' DECLARE @SQL1 nvarchar(MAX)' +
            ' SELECT @TotalLogSpace = (SUM(CONVERT(dec(17, 2), sysfiles.size)) / 128) FROM dbo.sysfiles AS sysfiles WHERE [groupid] = 0' +
            ' SELECT @FreeLogSpace = (SUM((size - FILEPROPERTY(name, ''SpaceUsed'')))) / 128.0 FROM sys.database_files WHERE [type] = 1' +
            ' SELECT @filename = name FROM sys.database_files WHERE [type] = 1' +
            ' SET @CanshrinkSize = CAST((@TotalLogSpace - @FreeLogSpace) AS BIGINT)' +
            ' SET @SQL1 = ''USE [' + @DBNAME + ']''' +
            ' SET @SQL1 = @SQL1 + ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize + 1 AS NVARCHAR(MAX)) + '')''' +
            ' EXEC (@SQL1)'
        EXEC (@SQL)
        FETCH NEXT FROM @itemCur INTO @DBNAME
    END
    CLOSE @itemCur
    DEALLOCATE @itemCur
    SELECT * FROM [#DataBaseServerData]
    DROP TABLE [#DataBaseServerData]
END

根本原因分析

根據報錯信息定位到具體數據庫,執行 DBCC LOGINFO 查看虛擬日志文件(VLF)狀態:

dbcc loginfo(N'cdb')

結果顯示該數據庫僅有兩個 VLF,無法進一步收縮。因為腳本採用批量處理,一個數據庫失敗會導致後續數據庫不再執行收縮。

解決方案

在收縮前增加 VLF 數量判斷,只有當 VLF 數量大於 2 時才執行收縮操作。修改後的邏輯如下圖所示:

具體實現可在腳本中加入查詢 DBCC LOGINFO 並統計 VLF 行數的步驟,若行數 <= 2 則跳過該數據庫。

總結

日志文件無法收縮的核心原因是 VLF 過少。通過預先檢查 VLF 數量,可避免批量作業中斷,提升維護效率。如有更好的方案歡迎交流分享。

copyright © 萬盛學電腦網 all rights reserved