代碼如下:

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')) --不需要進行日志收縮的數(shù)據(jù)庫名 
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語句實現(xiàn)Sql Server 2005日志收縮(批量)

復制代碼代碼如下:

-->Title:生成測試數(shù)據(jù) 
-->Author:wufeng4552 
-->Date :2009-09-15 08:56:03 
declare @dbname nvarchar(20) --數(shù)據(jù)庫名稱 
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'))--不需要進行日志收縮的數(shù)據(jù)庫名 
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 '+ 
--收縮指定數(shù)據(jù)文件 
N' dbcc shrinkfile(@logid)' 
exec(@sql) 
end 
fetch next from sysdbname into @dbname 
end 
close sysdbname 
deallocate sysdbname