批量收缩数据库日志文件

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