最近服務(wù)器執(zhí)行收縮日志文件大小的job老是報錯

我所用的一個批量收縮日志腳本

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)
--臨時表保存數(shù)據(jù)
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 

幸虧報錯信息還是很全面，根據(jù)報錯信息找到相關(guān)的數(shù)據(jù)庫，執(zhí)行一下DBCC LOGINFO

dbcc loginfo(N'cdb')

發(fā)現(xiàn)確實只有兩個VLF文件，不能再收縮了，因為是批量腳本，當(dāng)其中有一個庫失敗之后，后續(xù)的庫就不會再進行收縮操作

這里只要加上數(shù)據(jù)庫的VLF數(shù)量的判斷就可以了

本文寫的不好，還請各位大俠提出寶貴意見，如有好的解決方案歡迎分享，大家共同學(xué)習(xí)進步。

您可能感興趣的文章: