RuntimeStatusDetail12 Table Cleanup Issue – Stale Records Not Removed by Cleanup VST Task

Products

• CA Identity Suite

Issue/Introduction

Users may observe excessive growth of the 'RuntimeStatusDetail12' table in the Identity Suite database. Despite the daily Cleanup VST task running, stale records continue to accumulate and are not purged as expected. In some cases, this table can grow to millions of records, potentially impacting database performance and storage.

Environment

Release : 14.5
Component : Identity Manager

Cause

The issue is typically caused by the creation of "orphan" records in the 'RuntimeStatusDetail12' (rsd12) table. 

Resolution

Engineering team provided a hot fix to resolve this issue as part of defect DE676864. If you face the same issue then please create a support ticket and request for the fix.

Additional Information

Manual Cleanup of Existing Orphan Records

For existing stale records, a manual cleanup script may be required. 

Always test cleanup scripts in a lower environment (PRE/Development) before executing in Production. Ensure a full database backup is available.

Example Cleanup Logic:
The following logic identifies rows in 'runtimeStatusDetail12' that do not have matching entries in task sessions or event tables (both TP and Archive):

DELETE FROM runtimeStatusDetail12 t 
WHERE NOT EXISTS (SELECT 1 FROM tasksession12_5 ts WHERE ts.tasksessionid = t.eventID)
  AND NOT EXISTS (SELECT 1 FROM event12_5 te WHERE te.eventid = t.eventID)
  AND NOT EXISTS (SELECT 1 FROM archive_tasksession12_5 tsa WHERE tsa.tasksessionid = t.eventID)
  AND NOT EXISTS (SELECT 1 FROM archive_event12_5 tea WHERE tea.eventid = t.eventID)
  AND NOT EXISTS (SELECT 1 FROM object12_5 o WHERE o.objectid = t.eventID);

Note: For large tables (e.g., >1 million rows), it is recommended to run the deletion in batches and use a 'DRY_RUN' mode to validate the row count first.