SQL语句实现Sql Server 2005日志收缩(批量)

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-15 08:56:03
declare @dbname nvarchar(20) --數據庫名稱
declare @sql nvarchar(max)
declare sysdbname cursor for select name from master.dbo.sysdatabases
open sysdbname
fetch next from sysdbname into @dbname
while @@fetch_status=0
begin
   if (@dbname not in('xxx'))--不需要进行日志收缩的数据库名
      begin
         set @sql=--日誌文件ID
                  N' declare @logid int '+
                  N' use '+@dbname+
                  N' select @logid=fileid from sysfiles where right(ltrim(rtrim(filename)),3)=''ldf'''+
                  --截断事务日志
                  N' backup log '+@dbname+' with no_log '+
                  --收缩指定数据文件
                  N' dbcc shrinkfile(@logid)'
                  exec(@sql)
      end
fetch next from sysdbname into @dbname
end
close sysdbname
deallocate sysdbname