SQL Space limit reached

May 21, 2008 at 5:28 PM
Hi,

I'm running Polymon version 1.0 with SQL 2005 Express edition.  I set my retention schedule last year to the default levels and now I've hit the 4GB limit for the database file and Polymon is no longer running.  I've gone back through all the monitors and lowered the retention months to a more acceptable limit, but that hasn't cleared out the old data from the database.  Is there a stored procedure or something I could use to go back through the database and delete old records?  My own disclaimer is that I'm not a DBA, so any help would be greatly appreciated.
Coordinator
May 22, 2008 at 1:02 PM

Hi,

The retention parameters that you set actually get applied by a job that should have been set up automatiucally during the install process. Disk space will not be reclaimed until that job runs.

Alternatively you can run the stored procedure that does the actual cleanup directly against your database (in fact, if there is a lot of data to celan up you may want to run it for the first time directly against the database as you may otherwise encounter timeout issues). The stroed procedure that should be run is called: 

 

agg_Monitor_ApplyRetentionScheme

No parameters are needed, just it run straight against your database. Depending on how much data there is this can take a while to run - just let it complete.

Keep in mind also that the majority of space is taken by the raw data since that maintains data points for each time the monitor is run. Daily, Weekly and Monthly rollups take far less space comparatively than the raw data. Depending on the monitor, I will usually set Daily, Weekly and Monthly retentions to fairly high numbers and then restrict raw data to just a few months.

Hope this helps - if you still have issues let me know.

May 24, 2008 at 6:04 PM
I've found that on larger databases that that job will never finish if run from inside PolyMon.  You will want to run it by hand the first time and maybe setup a scheduled task to run it automatically after that.  I personally keep 3 months of raw and 120 months of everything else.  You might think that this is too long, but I don't want to lose the rolled up data unless I tell it to.  It frequently takes a few hours for this job to run.  The problem is monitoring stops while this runs.  As a work around I've done the following:
"alter database PolyMon set read_committed_snapshot on"
This allows other transactions to still see the data at the same state as before the job started, but without blocking.  There can be side effects from this, but I've yet to see any in PolyMon.
Nov 3, 2010 at 7:58 PM

Hi,  I'm having this space issue again.  I run the following command from managment studio and get an error:

exec

agg_Monitor_ApplyRetentionScheme

Msg 201, Level 16, State 4, Procedure agg_Monitor_ApplyRetentionScheme, Line 0

Procedure or Function 'agg_Monitor_ApplyRetentionScheme' expects parameter '@StatusCode', which was not supplied.

I thought there were no input paramenters needed to run this stored proc, or am I doing it wrong?

Also any news on version 2?

Coordinator
Nov 3, 2010 at 8:17 PM

I think you may be confused with how procedures work.  It doesn't take any input parameters per se.  What it is doing is taking "output" parameters.  So, the direction of the parameters you are sending will be filled with values when the stored procedure returns.  here are the input parameters of the sproc...

PROCEDURE [dbo].[agg_Monitor_ApplyRetentionScheme]
 @StatusCode tinyint output,
 @StatusMessage varchar(8000) output
AS
BEGIN......

The @StatusCode is expected, because the sproc will fill this with an integer, hence the reason it has the word 'output' after it is declared.  Same with the @StatusMessage. Something like this should work in a SQL query window...

Declare @MyStatusCode tinyint
Declare @MyStatusMessage varchar(8000)
exec agg_Monitor_ApplyRetentionScheme @MyStatusCode, @MyStatusMessage

When the sproc completes, you might have values in your two variables.  While the sproc doesn't take input, it does take parameters that it will fill.  Try that and get back to us.

Nov 3, 2010 at 9:35 PM

The stored proc runs now, thanks!  However now it returns 0 rows deleted for all counters.  My retention scheme is set to keep only 1 month raw data, 12 months daily aggs, 24 months weekly ags and 36 months of monthly aggs.  So I know there's something to delete.  Any ideas?

Coordinator
Nov 8, 2010 at 4:57 PM

Can you post the sql script contained in you agg_Monitor_ApplyRetentionScheme stored proc on the SQL server? The counter info is typically returned in a select statement and I just want to make sure that section of the code looks OK.

Keep in mind that if you have run it recently it may actually be valid for it not delete any data until after the retention period has been surpassed for the rollups.

 

Also, agg_Monitor_ApplyRetentionScheme is a wrapper (tailored to PolyMon Manager output requirements) around a stored proc that does the actual work: agg_ApplyRetentionScheme

Take a look at that script: you can run it in debug mode by setting the @Debug bit to 1. This will do a dry run, not actually deleting any data, but instead returning the number of records it would have deleted in each case. Should give you an indication of whether there is any data to delete.

Nov 12, 2010 at 7:02 PM

Thanks for the post.  The debug bit helped us figure out what was going on.  Appears we were looking in the wrong direction for space to free up.  The MonitorEvent Table is the one taking up all the space.  Can we delete rows out of that table without any trickle down affect, or are there any primary/foriegn keys in this table we need to worry about?

Coordinator
Nov 17, 2010 at 8:27 PM

The MonitorEvent table is one of the tables that stores the "raw" monitoring data. It should also be cleaned up by the retention schedule (corresponds to the "raw" retention option) - setting it to something small should reduce the storage requirements for it. Rolled up data will still remain available for as long as the retention schedule allows for each individual rollup. However, keep in mind that each Monitor has separate settings for the retention schedule - so setting the default retention schedule will only apply new settings to new monitors, not existing ones. You can see how the sql stored proc agg_ApplyRetentionScheme deletes rows from the MonitorEvent table.

Nov 17, 2010 at 9:43 PM

Here is the stored Proc.  We didn't see anything in there that references the MonitorEvent Tables:

 

USE [PolyMon]
GO


/****** Object:  StoredProcedure [dbo].[agg_ApplyRetentionScheme]    Script Date: 11/17/2010 13:38:02 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================

-- Author:        Fred Baptiste

-- Create date: 2/28/2007

-- Description:   Applies Retention Scheme to Raw and Agg tables

-- =============================================

CREATE PROCEDURE [dbo].[agg_ApplyRetentionScheme]
      @IsDebug bit = 1,
      @StatusMonthlyCount int output,
      @CounterMonthlyCount int output,
      @StatusWeeklyCount int output,
      @CounterWeeklyCount int output,
      @StatusDailyCount int output,
      @CounterDailyCount int output,
      @StatusRawCount int output,
      @CounterRawCount int output,
      @ErrMsg varchar(200) output,
      @Err int output
AS
BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      --Default Retention Settings

      Declare @DefaultMaxRaw smallint
      Declare @DefaultMaxDaily smallint
      Declare @DefaultMaxWeekly smallint
      Declare @DefaultMaxMonthly smallint

      set @Err=0
      set @StatusMonthlyCount = 0
      set @CounterMonthlyCount = 0
      set @StatusWeeklyCount = 0
      set @CounterWeeklyCount = 0
      set @StatusDailyCount = 0
      set @CounterDailyCount = 0
      set @StatusRawCount = 0
      set @CounterRawCount = 0
      set @ErrMsg = NULL

      select @DefaultMaxRaw = RetentionMaxMonthsRaw,
            @DefaultMaxDaily = RetentionMaxMonthsDaily,
            @DefaultMaxWeekly = RetentionMaxMonthsWeekly,
            @DefaultMaxMonthly = RetentionMaxMonthsMonthly

      from SysSettings

      if object_id('tempdb..#RS') is not null

      drop table #RS

      create table #RS

      (

            MonitorID int primary key,
            MaxMonthsRaw int,
            MaxMonthsDaily int,
            MaxMonthsWeekly int,
            MaxMonthsMonthly int
      )

      insert #RS (MonitorID, MaxMonthsRaw, MaxMonthsDaily, MaxMonthsWeekly, MaxMonthsMonthly)

      select Monitor.MonitorID,

            coalesce(MRS.MaxMonthsRaw, @DefaultMaxRaw) as MaxMonthsRaw,
            coalesce(MRS.MaxMonthsDaily, @DefaultMaxMonthly) as MaxMonthsDaily,
            coalesce(MRS.MaxMonthsWeekly, @DefaultMaxWeekly) as MaxMonthsWeekly,
            coalesce(MRS.MaxMonthsMonthly, @DefaultMaxMonthly) as MaxMonthsMonthly

      from Monitor left join MonitorRetentionScheme MRS on Monitor.MonitorID=MRS.MonitorID

 

 

      if @IsDebug=0
            begin tran

 

     

      /****** Delete Monthly Aggs, Status ******/

      if @IsDebug=1
            begin
            print 'Deleting: Status - Monthly...'
            select count(*) as [AggStatus_Monthly]
            from AggStatus_Monthly Agg
                  inner join #RS RS on Agg.MonitorID=RS.MonitorID
                  inner join TSMonthly TS on Agg.TimespanID=TS.TimespanID
            where TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())
 
            set @StatusMonthlyCount=@@ROWCOUNT
            end
      else
            begin
            delete
            from AggStatus_Monthly
            from #RS RS, TSMonthly TS
            where AggStatus_Monthly.MonitorID=RS.MonitorID
            and AggStatus_Monthly.TimespanID=TS.TimespanID
            and TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())
 
            select @Err=@@ERROR, @StatusMonthlyCount=@@ROWCOUNT
            if @Err<>0
                  begin
                  rollback tran
                  set @StatusMonthlyCount = 0
                  set @CounterMonthlyCount = 0
                  set @StatusWeeklyCount = 0
                  set @CounterWeeklyCount = 0
                  set @StatusDailyCount = 0
                  set @CounterDailyCount = 0
                  set @StatusRawCount = 0
                  set @CounterRawCount = 0
                  set @ErrMsg = 'Error deleting from AggStatus_Monthly.'
                  if object_id('tempdb..#RS') is not null
                        drop table #RS
                  return -1
                  end
                  else
                        begin
                        Commit tran
                  end
            end
 
 
      /****** Delete Monthly Aggs, Counters ******/
      if @IsDebug=1
            begin

           print 'Deleting Counters - Monthly...'
            select count(*) as [AggCounter_Monthly]
            from AggCounter_Monthly Agg
                  inner join #RS RS on Agg.MonitorID=RS.MonitorID
                  inner join TSMonthly TS on Agg.TimespanID=TS.TimespanID
            where TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())
 
            set @CounterMonthlyCount=@@ROWCOUNT
            end
      else
            begin
            delete
            from AggCounter_Monthly
            from #RS RS, TSMonthly TS
            where AggCounter_Monthly.MonitorID=RS.MonitorID
                  and AggCounter_Monthly.TimespanID=TS.TimespanID
                  and TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())
 
            select @Err=@@ERROR, @CounterMonthlyCount=@@ROWCOUNT
            if @Err<>0
                  begin
                  rollback tran
                  set @StatusMonthlyCount = 0
                  set @CounterMonthlyCount = 0
                  set @StatusWeeklyCount = 0
                  set @CounterWeeklyCount = 0
                  set @StatusDailyCount = 0
                  set @CounterDailyCount = 0
                  set @StatusRawCount = 0
                  set @CounterRawCount = 0
                  set @ErrMsg = 'Error deleting from AggCounter_Monthly.'
                  if object_id('tempdb..#RS') is not null
                        drop table #RS
                  return -1
                  end
            end
END
GO

Coordinator
Nov 23, 2010 at 10:58 PM

There's an issue - the stored proc you copied above is not complete and is missing most of the code in that stored proc at the bottom (basically the code that clears weekly aggs, daily aggs and raw files as well as teh code that returns how many rows were deleted, etc back to PolyMon Manager). Not sure if that's indeed what is in your version of the sp on your server or if the copy of the code snippet did not work properly.

You can get the full stored proc from the source repository, but here it is in whole - as you can see there's quite a bit missing. That would also explain why PolyMon Manager is not reporting any rows as deleted and also why your tables have been growing even though you have retention schedules in place...

As you can see at the bottom of this proc below, data from the MonitorEvent, MonitorEventCounter tables, etc are being deleted according to the retention schedule.

Fred.

 

 

****** Object:  StoredProcedure [dbo].[agg_ApplyRetentionScheme]    Script Date: 05/08/2008 18:32:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Fred Baptiste
-- Create date: 2/28/2007
-- Description:	Applies Retention Scheme to Raw and Agg tables
-- =============================================
CREATE PROCEDURE [dbo].[agg_ApplyRetentionScheme] 
	@IsDebug bit = 1,
	@StatusMonthlyCount int output,
	@CounterMonthlyCount int output,
	@StatusWeeklyCount int output,
	@CounterWeeklyCount int output,
	@StatusDailyCount int output,
	@CounterDailyCount int output,
	@StatusRawCount int output,
	@CounterRawCount int output,
	@ErrMsg varchar(200) output,
	@Err int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Default Retention Settings
	Declare @DefaultMaxRaw smallint
	Declare @DefaultMaxDaily smallint
	Declare @DefaultMaxWeekly smallint
	Declare @DefaultMaxMonthly smallint


	set @Err=0
	set @StatusMonthlyCount = 0
	set @CounterMonthlyCount = 0
	set @StatusWeeklyCount = 0
	set @CounterWeeklyCount = 0
	set @StatusDailyCount = 0
	set @CounterDailyCount = 0
	set @StatusRawCount = 0
	set @CounterRawCount = 0
	set @ErrMsg = NULL

	select @DefaultMaxRaw = RetentionMaxMonthsRaw,
		@DefaultMaxDaily = RetentionMaxMonthsDaily,
		@DefaultMaxWeekly = RetentionMaxMonthsWeekly,
		@DefaultMaxMonthly = RetentionMaxMonthsMonthly
	from SysSettings

	if object_id('tempdb..#RS') is not null
	drop table #RS

	create table #RS
	(
		MonitorID int primary key,
		MaxMonthsRaw int,
		MaxMonthsDaily int,
		MaxMonthsWeekly int,
		MaxMonthsMonthly int
	)
	insert #RS (MonitorID, MaxMonthsRaw, MaxMonthsDaily, MaxMonthsWeekly, MaxMonthsMonthly)
	select Monitor.MonitorID, 
		coalesce(MRS.MaxMonthsRaw, @DefaultMaxRaw) as MaxMonthsRaw,
		coalesce(MRS.MaxMonthsDaily, @DefaultMaxMonthly) as MaxMonthsDaily,
		coalesce(MRS.MaxMonthsWeekly, @DefaultMaxWeekly) as MaxMonthsWeekly,
		coalesce(MRS.MaxMonthsMonthly, @DefaultMaxMonthly) as MaxMonthsMonthly
	from Monitor left join MonitorRetentionScheme MRS on Monitor.MonitorID=MRS.MonitorID


	if @IsDebug=0
		begin tran

	
	/****** Delete Monthly Aggs, Status ******/
	if @IsDebug=1
		begin
		print 'Deleting: Status - Monthly...'
		select count(*) as [AggStatus_Monthly]
		from AggStatus_Monthly Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSMonthly TS on Agg.TimespanID=TS.TimespanID
		where TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())

		set @StatusMonthlyCount=@@ROWCOUNT
		end
	else
		begin
		delete
		from AggStatus_Monthly
		from #RS RS, TSMonthly TS
		where AggStatus_Monthly.MonitorID=RS.MonitorID
		and AggStatus_Monthly.TimespanID=TS.TimespanID
		and TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())

		select @Err=@@ERROR, @StatusMonthlyCount=@@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggStatus_Monthly.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end
		end



	/****** Delete Monthly Aggs, Counters ******/
	if @IsDebug=1
		begin
		print 'Deleting Counters - Monthly...'
		select count(*) as [AggCounter_Monthly]
		from AggCounter_Monthly Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSMonthly TS on Agg.TimespanID=TS.TimespanID
		where TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())

		set @CounterMonthlyCount=@@ROWCOUNT
		end
	else
		begin
		delete
		from AggCounter_Monthly 
		from #RS RS, TSMonthly TS
		where AggCounter_Monthly.MonitorID=RS.MonitorID
			and AggCounter_Monthly.TimespanID=TS.TimespanID
			and TS.EndDT < dateadd(mm,-RS.MaxMonthsMonthly, getdate())

		select @Err=@@ERROR, @CounterMonthlyCount=@@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggCounter_Monthly.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end
		end


	/****** Delete Weekly Aggs, Status ******/
	if @IsDebug=1
		begin
		print 'Deleting Status - Weekly...'
		select count(*) as [AggStatus_Weekly]
		from AggStatus_Weekly Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSWeekly TS on Agg.TimespanID=TS.TimespanID
		where TS.EndDT < dateadd(mm,-RS.MaxMonthsWeekly, getdate())

		set @StatusWeeklyCount = @@ROWCOUNT
		end
	else
		begin
		delete
		from AggStatus_Weekly 
		from #RS RS, TSWeekly TS
		where AggStatus_Weekly.MonitorID=RS.MonitorID
			and AggStatus_Weekly.TimespanID=TS.TimespanID
			and TS.EndDT < dateadd(mm,-RS.MaxMonthsWeekly, getdate())

		select @Err=@@ERROR, @StatusWeeklyCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggStatus_Weekly.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end
		end

		

	/****** Delete Weekly Aggs, Counter ******/
	if @IsDebug=1
		begin
		print 'Deleting Counters - Weekly...'
		select count(*) as [AggCounter_Weekly]
		from AggCounter_Weekly Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSWeekly TS on Agg.TimespanID=TS.TimespanID
		where TS.EndDT < dateadd(mm,-RS.MaxMonthsWeekly, getdate())

		set @CounterWeeklyCount = @@ROWCOUNT		
		end
	else
		begin
		delete
		from AggCounter_Weekly
		from #RS RS, TSWeekly TS
		where AggCounter_Weekly.MonitorID=RS.MonitorID
			and AggCounter_Weekly.TimespanID=TS.TimespanID
			and TS.EndDT < dateadd(mm,-RS.MaxMonthsWeekly, getdate())

		select @Err=@@ERROR, @CounterWeeklyCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggCounter_Weekly.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end		
		end



	/****** Delete Daily Aggs, Status ******/
	if @IsDebug=1
		begin
		print 'Deleting Status - Daily...'
		select count(*) as [AggStatus_Daily]
		from AggStatus_Daily Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSDaily TS on Agg.TimespanID=TS.TimespanID
		where TS.DT < dateadd(mm,-RS.MaxMonthsDaily, getdate())

		set @StatusDailyCount = @@ROWCOUNT		
		end
	else
		begin
		delete
		from AggStatus_Daily
		from #RS RS, TSDaily TS
		where AggStatus_Daily.MonitorID=RS.MonitorID
			and AggStatus_Daily.TimespanID=TS.TimespanID
			and TS.DT < dateadd(mm,-RS.MaxMonthsDaily, getdate())

		select @Err=@@ERROR, @StatusDailyCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggStatus_Daily.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end		
		end



	/****** Delete Daily Aggs, Counters ******/
	if @IsDebug=1
		begin
		print 'Deleting Counters - Daily...'
		select count(*) as [AggCounter_Daily]
		from AggCounter_Daily Agg
			inner join #RS RS on Agg.MonitorID=RS.MonitorID
			inner join TSDaily TS on Agg.TimespanID=TS.TimespanID
		where TS.DT < dateadd(mm,-RS.MaxMonthsDaily, getdate())

		set @CounterDailyCount = @@ROWCOUNT		
		end
	else
		begin
		delete
		from AggCounter_Daily
		from #RS RS, TSDaily TS
		where AggCounter_Daily.MonitorID=RS.MonitorID
			and AggCounter_Daily.TimespanID=TS.TimespanID
			and TS.DT < dateadd(mm,-RS.MaxMonthsDaily, getdate())

		select @Err=@@ERROR, @CounterDailyCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from AggCounter_Daily.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end		
		end



	/****** Delete RawData, Counters ******/
	if @IsDebug=1
		begin
		print 'Deleting Counters - Raw...'
		select count(*) as [MonitorEventCounter] 
		from MonitorEventCounter with (index(IX_MonitorEvent2))
			inner join #RS RS on MonitorEventCounter.MonitorID=RS.MonitorID
		where MonitorEventCounter.EventDT < dateadd(mm,-RS.MaxMonthsRaw, getdate())

		set @CounterRawCount = @@ROWCOUNT		
		end
	else
		begin
		delete
		from MonitorEventCounter
		from #RS RS
		where MonitorEventCounter.MonitorID=RS.MonitorID
			and MonitorEventCounter.EventDT < dateadd(mm,-RS.MaxMonthsRaw, getdate())

		select @Err=@@ERROR, @CounterRawCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from MonitorEventCounter.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end		
		end


	/****** Delete RawData, Status ******/
	if @IsDebug=1
		begin
		print 'Deleting Status - Raw...'
		select count(*) as [MonitorEvent] 
		from MonitorEvent with (index(IX_MonitorEvent_1))
			inner join #RS RS on MonitorEvent.MonitorID=RS.MonitorID
		where MonitorEvent.EventDT < dateadd(mm,-RS.MaxMonthsRaw, getdate())

		set @StatusRawCount = @@ROWCOUNT		
		end
	else
		begin
		delete
		from MonitorEvent
		from #RS RS
		where MonitorEvent.MonitorID=RS.MonitorID
			and MonitorEvent.EventDT < dateadd(mm,-RS.MaxMonthsRaw, getdate())

		select @Err=@@ERROR, @StatusRawCount = @@ROWCOUNT
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error deleting from MonitorEvent.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end		
		end

	if @IsDebug=0
		begin
		commit tran

		set @Err=@@ERROR
		if @Err<>0
			begin
			rollback tran
			set @StatusMonthlyCount = 0
			set @CounterMonthlyCount = 0
			set @StatusWeeklyCount = 0
			set @CounterWeeklyCount = 0
			set @StatusDailyCount = 0
			set @CounterDailyCount = 0
			set @StatusRawCount = 0
			set @CounterRawCount = 0
			set @ErrMsg = 'Error committing transaction.'
			if object_id('tempdb..#RS') is not null
				drop table #RS
			return -1
			end
		end

	if object_id('tempdb..#RS') is not null
	drop table #RS
END
GO