SQL清除所有数据库日志脚本

--SQL清除所有数据库日志脚本
declare @CurrentDataBaseName nvarchar(100)
declare @CurrentDataBaseID nvarchar(100)
declare @CurrentLogName nvarchar(100)
declare @CurrentClearString nvarchar(4000)
declare @CurrentLogFileName nvarchar(255)
declare @CurrentClearLogString nvarchar(2000)
--查询所有数据库名称
--select * from master..sysdatabases where dbid>=7
declare tb cursor local for select name,dbid from master..sysdatabases where dbid>=7;
open tb
fetch next from tb into @CurrentDataBaseName,@CurrentDataBaseID
while @@fetch_status=0
begin
--查询指定数据库对应的所有日志文件
--select * from sys.database_files
--使用游标查询数据库对应的日志文件
set @CurrentLogFileName=''
set @CurrentClearLogString=''
--select * from sysaltfiles where dbid=7 and status>2;
declare tf cursor local for select name from sysaltfiles where dbid=@CurrentDataBaseID and status>2 and groupid<>1;
open tf
fetch next from tf into @CurrentLogFileName
while @@fetch_status=0
begin
if @CurrentClearLogString<>''
begin
set @CurrentClearLogString+='
DBCC SHRINKFILE (['+@CurrentLogFileName+'] , 11, TRUNCATEONLY)'
--print @CurrentClearLogString
end
else
begin
set @CurrentClearLogString='
DBCC SHRINKFILE (['+@CurrentLogFileName+'] , 11, TRUNCATEONLY)'
end
--print '当前清除日志文件语句'+@CurrentClearLogString
fetch next from tf into @CurrentLogFileName
end
close tf
deallocate tf
--print @CurrentClearLogString;

set @CurrentClearString='
USE [master]
ALTER DATABASE ['+@CurrentDataBaseName+'] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE ['+@CurrentDataBaseName+'] SET RECOVERY SIMPLE
USE ['+@CurrentDataBaseName+']'+@CurrentClearLogString+'
USE [master]
ALTER DATABASE ['+@CurrentDataBaseName+'] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE ['+@CurrentDataBaseName+'] SET RECOVERY FULL'
print @CurrentClearString;
exec sp_executesql @CurrentClearString;

print '清除数据库'+@CurrentDataBaseName +'日志完成,操作语句'
fetch next from tb into @CurrentDataBaseName,@CurrentDataBaseID
end
close tb
deallocate tb

print '清除所有数据库日志完成'