--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 '清除所有数据库日志完成'