SQL2008清除所有数据库日志脚本【鸡蛋】

use master

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

转自：http://blog.csdn.net/xqf222/article/details/7440627     

尊重原创

--清除单个数据库日志
    USE [master]   
        GO   
        ALTER DATABASE [KLWebsiteGlobal3.0] SET RECOVERY SIMPLE WITH NO_WAIT   
        GO   
        ALTER DATABASE [KLWebsiteGlobal3.0] SET RECOVERY SIMPLE   --简单模式   
        GO   
        USE [KLWebsiteGlobal3.0]    
        GO   
        DBCC SHRINKFILE (N'KLWebsiteGlobal3.0_log' , 11, TRUNCATEONLY)   
        GO   
        USE [master]   
        GO   
      
        ALTER DATABASE [KLWebsiteGlobal3.0] SET RECOVERY FULL WITH NO_WAIT   
      
        GO   
      
        ALTER DATABASE [KLWebsiteGlobal3.0] SET RECOVERY FULL  --还原为完全模式   
      
        GO