复制代码 代码如下:

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