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

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

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 

代码如下:

-->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 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!