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

最新推荐文章于 2025-01-15 17:50:07 发布

转载最新推荐文章于 2025-01-15 17:50:07 发布 · 131 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xuxiaoshuan/p/3247601.html

文章标签：

#数据库

本文详细介绍了如何使用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 '清除所有数据库日志完成'

转载于:https://www.cnblogs.com/xuxiaoshuan/p/3247601.html