SQL Server无法收缩日志文件的原因分析及解决办法
16lz
2022-01-08
最近服务器执行收缩日志文件大小的job老是报错
我所用的一个批量收缩日志脚本
USE [master]GO/****** Object: StoredProcedure [dbo].[ShrinkUser_DATABASESLogFile] Script Date: 01/05/2016 09:52:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROC [dbo].[ShrinkUser_DATABASESLogFile]ASBEGIN 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 CURSORSET @itemCur = CURSOR FOR SELECT name from SYS.[databases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution')and state=0OPEN @itemCurFETCH NEXT FROM @itemCur INTO @DBNAMEWHILE @@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]=0SELECT @FreeLogSpace = ( SUM(( size - FILEPROPERTY(name, ''SpaceUsed'') )) )/ 128.0 FROM sys.database_files WHERE [type] = 1SELECT @filename=name FROM sys.database_files WHERE [type]=1SET @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 @itemCurDEALLOCATE @itemCurSELECT * FROM [#DataBaseServerData]DROP TABLE [#DataBaseServerData]END
dbcc loginfo(N'cdb')
发现确实只有两个VLF文件,不能再收缩了,因为是批量脚本,当其中有一个库失败之后,后续的库就不会再进行收缩操作
这里只要加上数据库的VLF数量的判断就可以了
本文写的不好,还请各位大侠提出宝贵意见,如有好的解决方案欢迎分享,大家共同学习进步。
更多相关文章
- Android,LIstView中的OnItemClick点击无效的解决办法
- 华为手机Android(安卓)Studio开发不显示Logcat解决办法
- 安卓9.0 http请求数据失败解决办法
- Android(安卓)Studio 导入包时报 Duplicate files copied in APK
- Android(安卓)启动时闪一下黑屏问题的解决办法
- JNI 无法确定Bitmap的签名
- android webview 中网页数据与js交互
- Android(安卓)webview Not allowed to load local resource异常
- Android(安卓)SDK Manager更新版慢解决办法