原理跟网上数据库清理日志一样,只是嵌套一个循环用来一键清理所有库的日志
代码如下:
--database_id是过滤系统数据库不参与清日志,先吧需要清理的数据库插入临时表
select name,0 as flag into #aaa from sys.databases where database_id>6
--创建while循环,取出数据库名称带入执行
while exists(select 1 from #aaa where flag=0)
begin
select top 1 * into #bbb from #aaa where flag=0
declare @database varchar(100)
declare @name nvarchar(100)
select @database=name from #bbb
declare @sql_exec nvarchar(2000)
set @sql_exec= 'USE ['+@database+']
ALTER DATABASE '+@database+' SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE '+@database+' SET RECOVERY SIMPLE --简单模式
USE '+@database+'
declare @name varchar(600)
declare @sql nvarchar(1000)
SELECT @name=name FROM sys.database_files where file_id=2;
set @sql='' DBCC SHRINKFILE (N''''''+@name+'''''', 11, TRUNCATEONLY)''
exec sp_executesql @sql
USE [master]
ALTER DATABASE '+@database+' SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE '+@database+' SET RECOVERY FULL --还原为完全模式
'
exec sp_executesql @sql_exec
update #aaa set flag=1 where name =@database
drop table #bbb
end
drop table #aaa