由于在生产环境中数据库很多，又缺乏管理，导致数据库日志不断增加，且磁盘空间逐渐减少，手动收缩又太麻烦，写个脚本批量执行下：
代码
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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。