SQL清除所有数据库日志脚本代码,CSharp代码库,德仔网

代码语言

代码分类

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

作者:xqf222 / 发布于2014/2/25/ 312

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

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　数据库日志　

同语言下

可能有用的

xqf222贡献的其它代码(82)