我所用的一个批量收缩日志脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
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文件,不能再收缩了,因为是批量脚本,当其中有一个库失败之后,后续的库就不会再进行收缩操作
这里只要加上数据库的VLF数量的判断就可以了
本文写的不好,还请各位大侠提出宝贵意见,如有好的解决方案欢迎分享,大家共同学习进步。