Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

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

Orphaned data in CONSW and CONSS

Created by Interspire Import, last modified by Melanie Boyd on Jan 25, 2018

Views: 1,019 Votes: 0 Revisions: 10

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

Tags

Classifications

Public