對原有SP_SHRINKFILE存儲過程進行改進,替代原來系統表。代碼如下: /*==================================================== 功能:壓縮server上所有資料庫LOG檔 創建時間:2009/11/2 創建者:fuming_dong 修改原因:将原来用的两个游标改为一个, 利用系统表SYSALTFILES来替代原来的SYSDATABASES和SYSFILES 修改人:fuming_dong =====================================================*/ CREATE PROCEDURE sp_shrinkfile AS SET NOCOUNT ON create table #temp_table( dbname varchar(128), logname varchar(128)) insert #temp_table(dbname,logname) select DB_NAME(dbid),name from sysaltfiles where dbid not in(DB_ID('master'),DB_ID('model'),DB_ID('msdb'),DB_ID('northwind'),DB_ID('pubs'),DB_ID('tempdb')) and groupid ='0' declare cur_temp cursor local fast_forward read_only for select rtrim(dbname),rtrim(logname) from #temp_table declare @dbname varchar(128), @logname varchar(128), @sp_string varchar(128), @bak_string varchar(128), @dc_string varchar(128) open cur_temp fetch next from cur_temp into @dbname,@logname while @@fetch_status = 0 begin set @sp_string ='sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1' set @bak_string ='BACKUP LOG ' +@dbname+ ' WITH TRUNCATE_ONLY' set @dc_string ='use ' +@dbname+ ' dbcc shrinkfile(' +@logname+ ',0)' -- exec (@sp_string) -- 此系統存儲過程用於事務複製,在此暫時不執行 exec (@bak_string) exec (@dc_string) fetch next from cur_temp into @dbname,@logname end close cur_temp deallocate cur_temp truncate table #temp_table drop table #temp_table SET NOCOUNT OFF GO exec sp_procoption N'sp_shrinkfile', N'startup', N'true' GO