1 USE [EPPM] 2 GO 3 /****** Object: StoredProcedure [dbo].[REFRDEL_CLEANUP] Script Date: 2016/4/2 16:32:29 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[REFRDEL_CLEANUP] 9 (@pret_val integer OUTPUT, @pret_msg varchar(1000) OUTPUT ) AS 10 declare @curr_time datetime 11 declare @vdiff integer 12 declare @vinterval_date datetime 13 declare @vset varchar(20) 14 declare @vsetnum integer 15 declare @min_refrdel_date datetime 16 declare @delete_target datetime 17 declare @max_minutes_to_sweep integer 18 declare @interval_step integer 19 declare @cnt integer 20 declare @i integer 21 declare @total_cleared integer 22 begin try 23 set @curr_time=getdate() 24 set @cnt=0 25 set @i=1 26 set @pret_val=0 27 set @total_cleared=0 28 exec settings_read_number @max_minutes_to_sweep OUTPUT,'database.cleanup.Refrdel','DaysToDelete',1 29 exec settings_read_number @interval_step OUTPUT,'database.cleanup.Refrdel','IntervalStep',15 30 exec settings_read_string @vset OUTPUT,'database.cleanup.Refrdel','KeepInterval','5d' 31 set @max_minutes_to_sweep=@max_minutes_to_sweep*1440 32 exec get_interval_difference @vset, @vdiff OUTPUT 33 set @vinterval_date = @curr_time-@vdiff 34 print 'Keep Date: ' + convert(varchar(30),@vinterval_date) 35 select @min_refrdel_date=min(delete_date) from refrdel 36 print 'Oldest Refrdel: ' + convert(varchar(30),@min_refrdel_date) 37 set @delete_target = @min_refrdel_date 38 if @min_refrdel_date is not null 39 begin 40 set @total_cleared=0 41 while ( @i <= @max_minutes_to_sweep and @delete_target < @vinterval_date ) 42 begin 43 set @delete_target=dateadd(mi,@interval_step,@delete_target) 44 delete from refrdel where delete_date< @delete_target 45 set @total_cleared= @total_cleared +@@rowcount 46 set @i=@i+@interval_step 47 end 48 end 49 else 50 print 'Nothing to Delete' 51 set @pret_val =@total_cleared 52 set @pret_msg='Cleared: ' + ltrim(str(@pret_val)) + ' records from ' + convert(varchar(30),@min_refrdel_date) +' to ' + convert(varchar(30),@delete_target) 53 print @pret_msg 54 end try 55 Begin Catch 56 set @pret_val=error_number() 57 set @pret_msg=error_message() 58 End Catch