Purge Audit Trail Job Runs for a Very Long Time (or Never Completes) on Objects With Millions of Audit Records

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

On any Clarity PPM system where one or more audited objects (standard or custom) have accumulated audit history in the millions of rows per object, the Purge Audit Trail scheduled job can run for a very long time — many hours or longer — and may appear to make little or no progress until it either completes or is stopped.

This occurs even when the object's audit retention ("Purge audit history older than N days") is configured with a small, valid value, and is not caused by an unusually short or long retention setting.

Preconditions:

• Clarity PPM environment (SaaS or on-premise) on PostgreSQL.
• At least one audited object (standard or custom) whose audit history has grown into the millions of rows.
• A valid, non-zero "Purge Audit History" retention period already configured for that object.

Steps:

1. Identify an audited object with a large audit history — for example, group your audit table by object type to find which objects have accumulated several million or more rows.
2. Confirm that object has a valid, non-zero retention period configured (Studio > [Object] > Configure Audit Trail).
3. Trigger the Purge Audit Trail job (or note its next scheduled run time) and, for the duration of the run, monitor:
   • The row count / size for that object's audit records, checked periodically during the run.
   • (PostgreSQL) The purge job's database session, to see whether its transaction start time resets periodically (healthy/expected) or stays fixed for the entire run (indicates this defect).

Expected Result: The audit row count / size for the object being purged should decrease incrementally throughout the run — visibly dropping in increments of roughly 10,000 rows as each batch completes — rather than only at the very end. On PostgreSQL, the purge session's transaction start time should reset periodically during the run (a new, short transaction beginning after each batch is saved), and if the job is stopped partway through, only the most recent, not-yet-saved batch should be lost.

Actual Result: On an affected system, the audit row count / size for the object being purged does not visibly decrease during the run — it remains essentially unchanged until the job completes (if it completes within the available maintenance window at all), at which point the full reduction appears all at once. The purge session's transaction start time remains fixed for the entire duration of the run rather than resetting per batch, confirming the whole run executed as a single, long-lived operation. If the job is stopped or the server restarts partway through, all progress made so far is lost and must be redone, rather than only the most recent batch.

Environment

Clarity 16.4.1,16.4.2

Cause

DE210129

The Purge Audit Trail job deletes audit rows in batches of 10,000 rows via a database procedure, and is designed to save (commit) its progress after each batch — specifically so a large purge does not run as one very large, continuous database operation. In affected releases, that per-batch save step is guarded by an internal check that never evaluates as expected on the job's normal execution path, so the save step never actually happens during the run.

The 10,000-row batching itself still occurs, but none of the work becomes durable or externally visible until the entire purge for that object finishes. In practice:

• A purge covering millions of eligible rows runs as one very large, long-running operation instead of many short ones.
• No progress is visible externally (in database size, row counts, or monitoring tools) until the whole run completes.
• The larger an object's audit backlog, the more pronounced this becomes — objects with audit history in the tens or hundreds of millions of rows can take many hours per run, and the job effectively cannot make sustained progress within a typical maintenance window.

This reproduces for any object once its number of eligible-for-purge rows is large enough to span many batches — it is not related to which retention value (number of days) is configured, only to the volume of data being processed in a single run.

Resolution

DE210129, in review with engineering