Reduce DPA repository size

10/22/2018 11:59 AM

1/20/2026 10:24 PM

This article provides steps to reduce the size of your DPA repository and discuss how to know the largest tables and possible options to reduce the size of common large tables. DPA retains data based on the DPA repository data retention policy settings

About  90% of storage usage occurs within the first 30 days of collection per instance. Reducing the clean_days_of_detail setting is the biggest lever to pull to reduce your repository size.

Note: Changing this setting will affect the number of days displayed on the DPA Home page when clicking on an instance. By making this change, the second by second data will be summarized. This setting can no longer be reverted to display additional days. 

Database Performance Analyzer

DPA summarizes data to the ten minute and hour levels through out the day. The daily summary and delete of the oldest data runs as a job nightly. The default time for this task to run is set in each monitored instances advanced options menu in the setting CLEAN_START_HOUR. Default is 10 p.m. DPA application server local time. After the task runs with success each monitored DPA server will set a flag called LR_CLEAN. This is the "Last Run" for the cleaners. This can be seen in by navigating to

1. Options
2. advanced options
3. DB instance Options tab Select the instances from the drop down menu. 
4. check the "support options" check box in the upper right.
5. Look for LR_CLEAN

The same values for what is set in CLEAN_DAYS_OF_DETAIL, CLEAN_START_HOUR, and LR_CLEAN can also be seen in the table CONDPRM one row per ID of instance that is in the table COND or in log files in CSV files with those same names. 

changes made to any retention settings is not immediate as the next run of the cleaners would have to happen. again default 10 pm or setting in CLEAN_START_HOUR. .

1. Select the arrow on the monitored instance you want to change this option for. 
2. Click Advanced Options. 
3. Click DB Instance Option, and then select your target instance. 
4. Click CLEAN_DAYS_OF_DETAIL. This is a monitored instance specific option. Change it for all monitored instances that you want it to be non-default. The default value is 30 days.

* You may need to shrink your database datafiles in order to reclaim this space after a successful run of the cleaner process with the settings changed and the LR_CLEAN time has run. 

It is good to first check LR_CLEAN and make sure that it has a recent date as dates in the past could indicate that cleaners for a monitored instance are failing. This can be done from the logs looking at CONDPRM.CSV file filtering on LR_CLEAN from the DPA GUI in steps listed above or with the following query to the DPA repository. 
NOTE: LR_CLEAN can be in the past for monitors that have been in stopped state for some time or that are not licensed. DPA keeps data based on CLEAN_DAYS_OF_DETAIL and will keep the number of days set but, will not clear data if the monitor is off and has not added days to exceed that number. ie. A monitored instance is set to collect 30 days the default and has been off for 7 it would be expected that LR_CLEAN would not show a current date. 


If it is found that the LR_CLEAN is behind the next steps is to see if there are errors for clean database failed in the logs. Looking in idc.log errors might be similar to 
ERROR (2022-01-20T23:08:05,847-0500) [lowPriorityExecutor-thread-155] {name=SampleDB} AbstractCleanerService:358 - Clean database failed due to [PreparedStatementCallback; SQL [select PLAN_HASH_VALUE from CONPT_12 pt where PLAN_HASH_VALUE not in (select distinct PLANHASH from CON_PLAN_SUM_12 where DATEHOUR >= (select min(DATEHOUR) from CON_SAMPLE_SUM_12)) and PLAN_HASH_VALUE not in (select distinct ORPH from CONSW_12 where D > ? and ORPH is not null) and pt.ID = 0]; (conn=435) Connection timed out

The next step if this is seen is to know table sizes. 
Starting in DPA 2022.3 there is a location to see the table sizes for the DPA repository in the DPA Options page. Navigate to the support section Diagnostics page found in the lower left then click on the tables tab. 
This data is also in the log files sent. In a support log file zip  the files are in dpa\diagnostics\repositoryTablesInfo.log 


Knowing what tables are the largest is helpful for reducing the size. Common tables that are large are listed here with links on what can be done in settings or items to reduce them. 

In all tables below  _X is some number is a stats table for each monitored instance where the ID is on the table COND one per instance that you are monitoring. 

CONSS_X these tables are statistics such as execution counts for each instance. 
CONSPH_X these tables are for plan data on monitored SQL server instances. 
CONPT_X these tables are for plan data on monitored Oracle server instances. 
CONV_METRICS_DETAIL_X these are detail tables for the metrics from the optional VM add on data.

If you are on a version of DPA that is prior to DPA 2022.3 where the table size diagnostic information was added you can use the following query items to get this information. Note that there is one query per RDBMS repo type. Select the query for your repository type. 

-- ms sql / azure
SELECT t.NAME AS "TABLE_NAME",
       CONVERT(decimal(10, 2), ROUND((SUM(a.total_pages) * 8.) / 1024, 2)) AS "SIZE_MB",
       SUM(p.rows) AS "ROWS"
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
  AND i.OBJECT_ID > 255
  AND i.index_id <= 1
GROUP BY t.NAME,
         i.object_id,
         i.index_id,
         i.name
ORDER BY SIZE_MB DESC;

-- mysql
SELECT TABLE_NAME AS "TABLE_NAME",
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "SIZE_MB",
       TABLE_ROWS AS "ROWS"
FROM information_schema.TABLES
WHERE table_schema = 'dpa_repository'
ORDER BY (data_length + index_length) DESC;

-- oracle
SELECT TABLE_NAME AS "TABLE_NAME",
       round((num_rows*avg_row_len)/(1024*1024), 2) AS "SIZE_MB",
       num_rows AS "ROWS"
FROM all_tables
WHERE OWNER NOT LIKE 'SYS%'
  AND num_rows > 0
ORDER BY SIZE_MB DESC;

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.