MSSQL清理所有用户数据库日志(SQLSERVER2008)

use [master];
set nocount on;

declare @tbname varchar(50)='';
declare @tblog varchar(100)='';
declare @clearsql varchar(max)='';

declare @tmp_while_id int=0;
select tmp_while_id=identity(int,1,1),tmp_while_flag=0,
t.tbname,t.tblog
into #tmp_while
from
(
select master.sys.databases.name as tbname, master.sys.master_files.name as tblog
from master.sys.master_files inner join master.sys.databases on master.sys.master_files.database_id = master.sys.databases.database_id
where (master.sys.databases.owner_sid <> 0x01) and (master.sys.master_files.type = 1)
) as t

select @tmp_while_id=min(tmp_while_id) from #tmp_while where tmp_while_flag=0;
while @tmp_while_id is not null
begin
    select @tbname=tbname,@tblog=tblog from #tmp_while where tmp_while_id=@tmp_while_id;
    
    set @clearsql=@clearsql+'use [master];'+char(10);
    set @clearsql=@clearsql+'alter database ['+@tbname+'] set recovery simple with no_wait;'+char(10);
    set @clearsql=@clearsql+'alter database ['+@tbname+'] set recovery simple;'+char(10);
    set @clearsql=@clearsql+'use ['+@tbname+'];'+char(10);
    set @clearsql=@clearsql+'dbcc shrinkfile ('''+@tblog+''',1,truncateonly);'+char(10);
    set @clearsql=@clearsql+'use [master];'+char(10);
    set @clearsql=@clearsql+'alter database ['+@tbname+'] set recovery full with no_wait;'+char(10);
    set @clearsql=@clearsql+'alter database ['+@tbname+'] set recovery full;'+char(10);
    
    update #tmp_while set tmp_while_flag=1 where tmp_while_id=@tmp_while_id;
    select @tmp_while_id=min(tmp_while_id) from #tmp_while where tmp_while_flag=0 and tmp_while_id>@tmp_while_id;
end

drop table #tmp_while;
--print @clearsql;
exec(@clearsql);