复制代码 代码如下: 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 @nam
复制代码 代码如下: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
