全自動壓縮服務器上所有數據庫LOG檔

 背景：因管理服務器有點多，查看日誌佔用磁盤空間，一邊對日誌進行壓縮，故寫一SP讓其按排程執行，完 整代碼如下：/*==================================================== 功能：壓縮server上所有資料庫LOG檔 創建時間：2009/10/20 創建者：fuming_dong 修改原因： 修改人： =====================================================*/ CREATE PROCEDURE sp_shrinkfile AS set nocount on create table #temp_table( dbname varchar(128), dbid smallint) create table #temp_table1( Logname varchar(128), dbid smallint) insert #temp_table (dbname,dbid) select [name],dbid from master.dbo. sysdatabases where name not in ('master','model','msdb','northwind','pubs','tempdb') declare cur_temp_1 cursor local fast_forward read_only for select rtrim(dbname) from #temp_table declare @dbname varchar(128), @sqlstring varchar(180), @rep_str varchar(128), @bak_str varchar(128) open cur_temp_1 fetch next from cur_temp_1 into @dbname while @@fetch_status= 0 begin set @sqlstring ='select a.name,db_id('''+@dbname+''')from ['+@dbname+'].dbo.sysfiles a,master.dbo.sysdatabases b where b.name = db_name() and a.fileid <> 1' -- set @rep_str = ' sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1' --此系統存儲過程用於事務複製，在此暫時不執行 set @bak_str = ' backup log '+@dbname+' with truncate_only' -- exec (@rep_str) exec (@bak_str) insert #temp_table1 (Logname,dbid) exec(@sqlstring) fetch next from cur_temp_1 into @dbname end close cur_temp_1 deallocate cur_temp_1 declare cur_temp_2 cursor local fast_forward read_only for select * from #temp_table1 where Logname not like '%data%' declare @shr_str varchar(128), @logname varchar(128), @dbid smallint open cur_temp_2 fetch next from cur_temp_2 into @logname ,@dbid while @@fetch_status = 0 begin set @shr_str ='use '+RTRIM(db_name(@dbid))+' DBCC SHRINKFILE ('+RTRIM(@logname)+',0)' EXEC (@shr_str) fetch next from cur_temp_2 into @logname,@dbid end close cur_temp_2 deallocate cur_temp_2 truncate table #temp_table truncate table #temp_table1 drop table #temp_table drop table #temp_table1 set nocount off GO