Purging repository data

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 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 than 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 a 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: Use the value 0 in the Purge data older than when all collected data has to be purged from database

After the data purging process is completed, the notification will appear with indication Success!

Once the repository purging is executed and repository database data purged, can I restore it?

No. Repository data purging will permanently erase and remove data from the repository database

What are the default parameters of the ApexSQL monitoring tools repository database?

The default repository database parameters set by application during the installation process are as follows:

• Recovery model: Simple
• PRIMARY size: 1,000 MB
• PRIMARY Autogrowth: By 10%
• PRIMARY Maxsize: Unlimited
• Data file name: ApexSQLMonitor.mdf or ApexSQLBIMonitor.mdf or ApexSQLVMMonitor.mdf
• Transaction log initial size: Server default
• Transaction log Autogrowth: By 10%
• Transaction log Maxsize: Limited to 2000 MB
• Transaction log file name: ApexSQLMonitor_log.ldf or ApexSQLBIMonitor_log.ldf or ApexSQLVMMonitor_log.ldf

How does the data purging process remove data from the repository database?

In order to impose a minimal load on the database and on hosting SQL Server, especially in situations when large amounts of data have to be purged. ApexSQL monitoring tools purge data in batches of 50,000 rows until it purges all the data older than the specified number of days

An exception is when a value of 0 days is used.In this case, all ApexSQL monitoring tools will use the Truncate table operation for purging all data at once from affected tables, as the Truncate table is much faster and uses less system and transaction log resources

How often is the automated data purging process polled/executed in the background?

ApexSQL monitoring tools check every two hours for data older than the amount of days specified for the automatic purging and then delete those data

What data will be deleted from the repository database?

ApexSQL monitoring tools data purging feature will delete collected performance metrics data as well as alert data. ApexSQL Monitor will additionally delete collected wait statistic, query waits and index data

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.