Submit a ticketCall us

Welcome to the NEW Success Center. Search all resources (documentation, videos, training, knowledge base articles) or browse resources by product. If you are unable to find what you are looking for, please contact us at customersuccess@solarwinds.com

 

 

 

 

Home > Success Center > Database Performance Analyzer (DPA) > Orphaned data in CONSW and CONSS

Orphaned data in CONSW and CONSS

Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 855 Votes: 0 Revisions: 9

Overview

Under certain circumstances, old data can be orphaned (not cleaned) in a couple of DPA repository tables (CONSS and CONSW). When DPA detects orphaned rows, you will see an error message like this in the logs:

Orphaned rows detected in CONSS_15.

Note: In the above message, "15" is the monitored database ID, and CONSS_15 is the table containing orphaned rows.  You might also see messages for CONSW_xx tables.

This article explains how you can safely delete the orphaned rows.

 

Cleaning Orphaned Rows

1) Find Cutoff Date for Orphans:

To find out the cutoff date for orphaned records, you can run the following query (replace xx with the database id matching the message):

-- Oracle Repository
select trunc(min(IEDX)) from CONTT_xx

 

-- SQL Server Repository
select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx


You can safely delete any rows in the CONSS_xx and CONSW_xx tables that have dates older than the date returned by the above query. You may want to count the number of orphaned rows prior to deleting them. If there are a large number of rows, you may want to do them in batches.

 

2) Count Orphans:

-- Oracle Repository
select count(1) from CONSS_xx    -- or CONSW_xx
where D < (select trunc(min(IEDX)) from CONTT_xx)

 

-- SQL Server Repository
select count(1) from CONSS_xx    -- or CONSW_xx
where D < (select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx)

 

3) Clean Orphans:

If there are few enough orphaned rows (e.g. less than 250,000) you can safely delete them all at once:

-- Oracle Repository
delete from CONSS_xx    -- or CONSW_xx
where D < (select trunc(min(IEDX)) from CONTT_xx);
commit;

 

-- SQL Server Repository
delete from CONSS_xx    -- or CONSW_xx
where D < (select DATEADD(dd, DATEDIFF(dd, 0, MIN(IEDX)), 0) from CONTT_xx);
commit;
Last modified
19:17, 22 Jun 2016

Tags

Classifications

Public