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

以下是筆者使用的批量收縮日志預存程序:
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 數量,可避免批量作業中斷,提升維護效率。如有更好的方案歡迎交流分享。