最近服务器执行收缩日志文件大小的job老是报错
我所用的一个批量收缩日志脚本
sql;">
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
dbcc loginfo(N'cdb')
发现确实只有两个VLF文件，不能再收缩了，因为是批量脚本，当其中有一个库失败之后，后续的库就不会再进行收缩操作
解决方案欢迎分享，大家共同学习进步。
原文链接：https://www.f2er.com/mssql/62914.html