This article explains what repository data purging is, how to use it in ApexSQL Monitor, when and how it should be configured, what are the benefits that can be gained, and how it behaves in the background
ApexSQL Monitor might store a significant amount of data collected during the monitoring process into the ApexSQL Monitor repository database. To prevent ApexSQL Monitor repository database from growing too large, the ApexSQL Monitor data purging feature 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:
Select the Administration link in the main menu and open the Repository purging page
Select the Purge data older then 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)
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
Use the value 0 in the Purge data older then when all collected data have 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 Monitor 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
- 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
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 amount of data have to be purged. ApexSQL Monitor purges data in batches of 100,000 rows until it purges all the data older than specified number of days
An exception is when avalue of 0 days is used.In this case, ApexSQL Monitor 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 Monitor checks every two hours for data older than the amount of days specified for the automatic purging and then deletes those data
What data will be deleted from the repository database?
ApexSQL Monitor data purging will delete collected performance metrics data including System, SQL Server and database metrics, wait statistic, query waits and index data, as well as alert data
The volume of data stored in the ApexSQL Monitor 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 SQL Servers are additionally added for monitoring and/or Query waits data collecting at low minimum wait time configured.
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 Monitor deletes 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).
What is causing data purging to take so long?
After each repository purge is completed, ApexSQL Monitor will check the nonclustered index fragmentation of the ApexSQL.MonitorMeasuredValues table (the largest table of the repository database where the most of collected data are stored), and if it detects that the fragmentation level is larger than 80% it will automatically execute index rebuilding in order to improve the performance of the repository database. Rebuilding indexes can be a time-consuming operation as it depends on the amount of data in the table but also on the size of the index
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 (100000) FROM [ApexSQL].[MonitorAlerts] WHERE [TimeRaised] < DATEADD(day, - @NumberOfDays, SYSDATETIMEOFFSET()); IF @@ROWCOUNT = 0 BREAK; END WHILE (1 = 1) BEGIN DELETE TOP (100000) FROM [ApexSQL].[MonitorMeasuredValues] WHERE [MeasuredAt] < DATEADD(day, - @NumberOfDays, SYSDATETIMEOFFSET()); IF @@ROWCOUNT = 0 BREAK END WHILE (1 = 1) BEGIN DELETE TOP (100000) 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]; 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.