由于在生产环境中数据库很多,又缺乏管理,导致数据库日志不断增加,且磁盘空间逐渐减少,手动收缩又太麻烦,写个脚本批量执行下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE
@sql
VARCHAR
(
max
)
DECLARE @sqllog VARCHAR ( max )
DECLARE @dbNAME VARCHAR ( max )
DECLARE myCURSOR CURSOR FOR
SELECT ' use ' + NAME + ' DUMP TRANSACTION ' + NAME + ' WITH NO_LOG ' ,NAME FROM sys.databases
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @sql , @dbNAME
WHILE ( @@FETCH_status = 0 )
BEGIN
BEGIN TRY
EXEC ( @sql )
SET @sqllog = ' use ' + @dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=0
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC ( @sqllog )
SET @sqllog = ' use ' + @dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=1
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC ( @sqllog )
END TRY
BEGIN CATCH
PRINT ' ERROR '
END CATCH;
FETCH NEXT FROM myCURSOR INTO @sql , @dbNAME
END
CLOSE myCURSOR
deallocate myCURSOR
DECLARE @sqllog VARCHAR ( max )
DECLARE @dbNAME VARCHAR ( max )
DECLARE myCURSOR CURSOR FOR
SELECT ' use ' + NAME + ' DUMP TRANSACTION ' + NAME + ' WITH NO_LOG ' ,NAME FROM sys.databases
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @sql , @dbNAME
WHILE ( @@FETCH_status = 0 )
BEGIN
BEGIN TRY
EXEC ( @sql )
SET @sqllog = ' use ' + @dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=0
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC ( @sqllog )
SET @sqllog = ' use ' + @dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=1
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC ( @sqllog )
END TRY
BEGIN CATCH
PRINT ' ERROR '
END CATCH;
FETCH NEXT FROM myCURSOR INTO @sql , @dbNAME
END
CLOSE myCURSOR
deallocate myCURSOR