Purging repository data (4214752)

Applies to
ApexSQL Monitor, ApexSQL BI Monitor, ApexSQL VM Monitor

Summary
This article explains what repository data purging is, how to use it in ApexSQL monitoring tools, when and how it should be configured, what are the benefits that can be gained, and how it behaves in the background

Description

ApexSQL monitoring tools might store a significant amount of data collected during the monitoring process into the their repository databases. To prevent the repository database from growing too large, all monitoring tools have a data purging feature that allows the user to configure and schedule repository data purging for data older then the time specified by the user. The repository purging feature is primarily designed with an idea to keep the database size under control as a preventive action. The repository database purging can be performed manually by user or automatically by the application

Applies to
ApexSQL Monitor, ApexSQL BI Monitor, ApexSQL VM Monitor

Summary
This article explains what repository data purging is, how to use it in ApexSQL monitoring tools, when and how it should be configured, what are the benefits that can be gained, and how it behaves in the background

Description

ApexSQL monitoring tools might store a significant amount of data collected during the monitoring process into the their repository databases. To prevent the repository database from growing too large, all monitoring tools have a data purging feature that allows the user to configure and schedule repository data purging for data older then the time specified by the user. The repository purging feature is primarily designed with an idea to keep the database size under control as a preventive action. The repository database purging can be performed manually by user or automatically by the application

To configure automatic repository database purging:

1. Select the Administration link in the main menu and open the Repository purging page

   

2. Select the Purge data older than check box in the Automatic section and set the amount of days the stored performance monitoring data will be saved in the repository database (All data older that the specified amounts of day will be automatically purged)

   

3. Press Save and from now on the repository database will be regularly purged without requiring any user attention

To manually purge repository database data, use the same Repository purging page. In the Manual section of the page set the number of days, press the Purge data button

Quick tip: The volume of data stored in any ApexSQL monitoring tools' repository database can be limited by setting a lower number of days for which the data is stored (i.e. lower from 60 to 30 days). This is especially important when a large number of instances are additionally added for monitoring and/or when data collecting period is configured to minimum.

Why did the size on my disk remain the same after purging repository data?

The database size file on disk will not be changed by default after purging. ApexSQL monitoring tools delete data from the database that will allow free space for writing the new data without incising the file size any further, but the application will not shrink repository database/data files (it might impose excessive fragmentation of tables/indexes).

How can I purge the database myself?

To purge repository data older than specific number of days, you can use the following scripts

DECLARE @NumberOfDays INT = < Day_Number_Here >;

WHILE (1 = 1)
BEGIN
	DELETE TOP (50000)
	FROM [ApexSQL].[MonitorAlerts]
	WHERE [TimeRaised] < DATEADD(day, - @NumberOfDays, SYSDATETIMEOFFSET());

	IF @@ROWCOUNT = 0
		BREAK;
END

WHILE (1 = 1)
BEGIN
	DELETE TOP (50000)
	FROM [ApexSQL].[MonitorMeasuredValues]
	WHERE [MeasuredAt] < DATEADD(day, - @NumberOfDays, SYSDATETIMEOFFSET());

	IF @@ROWCOUNT = 0
		BREAK
END

--For ApexSQL Monitor only
WHILE (1 = 1)
BEGIN
	DELETE TOP (50000)
	FROM [ApexSQL].[MonitorQueryWaits]
	WHERE [MeasuredAt] < DATEADD(day, - @NumberOfDays, SYSDATETIMEOFFSET());

	IF @@ROWCOUNT = 0
		BREAK
END;

The <Day_Number_Here> in the script should be replaced with actual number of days for which data that are older than will be deleted

If all data should be purged from the repository database (equivalent to setting the 0 in the previous script), use the following

TRUNCATE TABLE [ApexSQL].[MonitorAlerts];

TRUNCATE TABLE [ApexSQL].[MonitorMeasuredValues];

--For ApexSQL Monitor only
TRUNCATE TABLE [ApexSQL].[MonitorQueryWaits];

How will data purging affect baselining?

The already calculated baselines will not be affected as baseline data is stored in a separate table that is not affected by purging.