批量收缩数据库日志文件

declare @sql nvarchar(max),@paramDef nvarchar(100)declare dbname scroll cursor for select name,recovery_model from sys.databases where database_id7open dbnamedeclare @dbname varchar(50),@recovery_model int,@db_log_file varchar(100)set @dbn

declare @sql nvarchar(max),@paramDef nvarchar(100)
declare dbname scroll cursor for 
 select name,recovery_model from sys.databases where database_id>7
open dbname

declare @dbname varchar(50),@recovery_model int,@db_log_file varchar(100)
set @dbname=''
fetch first from dbname into @dbname,@recovery_model
while @@FETCH_STATUS=0
begin
 --alter recovery model
 if (@recovery_model<>3)
 begin
 set @sql=N' alter database '+@dbname+' set RECOVERY SIMPLE '
 print 'Recovery :'+@sql
 exec sp_executesql @sql
 end
 --get log file of database
 set @sql=N'select @log_file=name from '+@dbname+'.sys.database_files where type=1'
 set @paramDef= N'@log_file varchar(100) OUTPUT '
 exec sp_executesql @sql,@paramDef,@log_file=@db_log_file output
 print @db_log_file 
 
 --shrink file
 set @sql= N'USE ['+@dbname+'] DBCC SHRINKFILE ('+@db_log_file+')'
 exec sp_executesql @sql
 
 fetch next from dbname into @dbname,@recovery_model
end
close dbname
deallocate dbname