SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)

175 阅读 0 评论 116 点赞

我是靠谱客的博主落寞奇异果，最近开发中收集的这篇文章主要介绍SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

复制代码代码如下:

DECLARE @name VARCHAR(25) 
DECLARE @SQL VARCHAR(1000) 
DECLARE @logid INT 

DECLARE sysdatabase_name CURSOR FOR SELECT name FROM master.dbo.sysdatabases 

OPEN sysdatabase_name 


FETCH NEXT FROM sysdatabase_name INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

IF ( @name NOT IN ('xxx')) --不需要进行日志收缩的数据库名 
BEGIN 
SET @SQL =' DECLARE @logid INT 
USE ' + @name+' 
SELECT @logid = fileid FROM sysfiles WHERE RIGHT(rtrim(filename),3) = ''ldf'' 

BACKUP LOG ' + @name+' WITH NO_LOG 
DBCC SHRINKFILE (@logid) ' 

EXEC(@SQL) 

END 

FETCH NEXT FROM sysdatabase_name INTO @name 

END 

CLOSE sysdatabase_name 
DEALLOCATE sysdatabase_name 

SQL语句实现Sql Server 2005日志收缩(批量)

复制代码代码如下:

-->Title:生成測試數據 
-->Author:wufeng4552 
-->Date :2009-09-15 08:56:03 
declare @dbname nvarchar(20) --數據庫名稱 
declare @sql nvarchar(max) 
declare sysdbname cursor for select name from master.dbo.sysdatabases 
open sysdbname 
fetch next from sysdbname into @dbname 
while @@fetch_status=0 
begin 
if (@dbname not in('xxx'))--不需要进行日志收缩的数据库名 
begin 
set @sql=--日誌文件ID 
N' declare @logid int '+ 
N' use '+@dbname+ 
N' select @logid=fileid from sysfiles where right(ltrim(rtrim(filename)),3)=''ldf'''+ 
--截断事务日志 
N' backup log '+@dbname+' with no_log '+ 
--收缩指定数据文件 
N' dbcc shrinkfile(@logid)' 
exec(@sql) 
end 
fetch next from sysdbname into @dbname 
end 
close sysdbname 
deallocate sysdbname 