当前位置： 移动技术网 > IT编程>数据库>MSSQL > MSSQL清理所有用户数据库日志(SQLSERVER2008)

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

2018年09月09日  | 移动技术网IT编程  | 我要评论

题西林壁 苏轼,李圣杰最近,情牵一线征婚网

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);

 

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网