DELETE queries failing on AUDIT_EVENT table

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The housekeeping which should run deletes every night is failing to complete, so the AUDIT_EVENT table has grown to
an unmanageable size, containing many millions of rows.
How can we get the housekeeping running successfully again ?

Environment

Release: CNMSPP99000-8.47-Unified Infrastructure Mgmt-Server Pack-- On Prem
Component:

Resolution

It has been noted that once the AUDIT_EVENT table starts to go into millions of rows, the housekeeping will likely fail due to the time taken for the queries to complete.
Deletes that would normally complete in a reasonable period of time (a few seconds to a few minutes) will never complete.
Attempting to run manual deletes against the table will also likely fail. 

In this situation, the only option is to backup both AUDIT_EVENT and AUDIT_CONFIG_CHANGE tables then truncate.
There is a relationship on the event_id column between the two tables, so attempting to drop one without dropping or otherwise cleaning out the other will break the referential integrity.
Both tables must be cleaned up.

deactivate audit probe 
truncate table AUDIT_CONFIG_CHANGE 
truncate table AUDIT_EVENT
activate audit