mssql 2008 批量 压缩日志

MSSQL数据库会随着时间，从而增大日志文件。从而过断时间要压缩下日志。但是MSSQL数据库新增、还原数据库会把数据库名、逻辑名搞得不一致。通过这个脚本可以自动生成所有库的压缩SQL脚本。本脚本排除了4个系统数据库master、tempdb、model、msdb；另外生成的这个脚本还有几个输出信息，就自己手动替换掉把。。

use master

declare @name varchar(max)
declare @dblogName varchar(max)
declare @i int
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('hufr_temp') AND type in ('U'))
begin
DROP TABLE hufr_temp
end
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('hufr_temp1') AND type in ('U'))
begin
DROP TABLE hufr_temp1
end


select name,identity(int,1,1) as num into hufr_temp FROM sys.databases where database_id>4 
select @i=COUNT(name) from hufr_temp
print 'use master'
while(@i>0)
begin
select @name=name from hufr_temp where num=@i
execute('select * into hufr_temp1  FROM ['+@name+'].sys.database_files')
select @dblogName=name FROM hufr_temp1 where type_desc='LOG'


--##########################################
print 'USE ['+@name+']'
print 'GO'
-- Truncate the log by changing the database recovery model to SIMPLE.
print 'ALTER DATABASE ['+@name+']'
print 'SET RECOVERY SIMPLE;'
print 'GO'
-- Shrink the truncated log file to 1 MB.
print 'DBCC SHRINKFILE (['+@dblogName+'], 10);'
print 'GO'
-- Reset the database recovery model.
print 'ALTER DATABASE ['+@name+']'
print 'SET RECOVERY FULL;'
print 'GO'
--##########################################


drop table  hufr_temp1
set @i-=1
end


--删除临时表
drop table hufr_temp
drop table hufr_temp1