How Long Should an Audit Trail Be Kept?

"How long should we keep the audit trail" looks like a simple question, but it hides the confusion of treating two different questions as one. This article separates them and explains how a chain structure changes the retention decision.

Retention Is Really Two Separate Questions

The first question is regulatory: how long are we required to keep the records? The answer varies by sector and record type and is usually measured in years.

The second is operational: how long can we keep them? That is about volume and cost, and in log platforms it is usually measured in months because the volume is large.

The confusion comes from assuming both questions need one answer. When application logs and governance records go into the same bag, the cost of the bulky one dictates the retention of the small one. The result is an audit trail kept for far less time than the regulator expects.

The Volume Is Smaller Than Expected

A governance record is not on the same scale as an application log. An application log produces rows per second; a governance record produces rows only for decisions and operations.

A practical measure: from opening to execution, a request typically produces ten to twenty audit events. An organisation raising a thousand requests a year is looking at roughly twenty thousand rows a year. Adding settings changes, session events and evidence exports multiplies that a few times over, and it is still small.

At that scale retention is not a cost problem. A five year audit trail is a few hundred thousand rows and a few gigabytes. The decision goes unmade not because of a technical constraint but because nobody considered it.

The Cost of Deleting From a Chain

Deleting old rows from an ordinary log table is harmless maintenance. In an audit trail linked by a chain it is different: deleting a record breaks the chain and verification reports a break at that point.

That is not a fault but the point of the design. The whole reason a chain exists is so that deletion does not stay silent. It does call for care when applying a retention policy, though: planned archiving should not look the same as unplanned tampering.

SQL Change Guard does not delete audit records on its own. There is no automatic purge job, and retention is left to the organisation's own database and archive policy. That is deliberate: a product quietly deleting its own audit records would break the most basic assumption of an audit trail.

How Archiving Should Work

If you do one day need to move old records out of the live database, three rules make it safe.

• Align the boundary with an anchor. The last record of the archived range should be the one covered by an anchor already delivered outside. That keeps the integrity of the archived portion demonstrable.
• Verify before moving. Run chain verification at the moment of archiving and record the result. That is the only way to tell later when an archive was corrupted.
• Record the archiving itself. Which range, when, on whose decision and where to. That record should stay in the live chain; otherwise the missing period cannot be explained to an auditor.

Moving the archive into a separate database written with insert only permission is good practice. If a full row copy of the records is already written to a second database, the archiving question is largely solved: a record removed from the live table remains in the copy.

Query Result Files Are a Separate Matter

The result files produced in a production query flow are a completely different category from audit records and should be treated in the opposite direction.

An audit record should be kept for a long time because it is evidence. A result file should be kept briefly because it may carry personal data and every extra day is a risk. So the retention period for result files is defined as a parameter and expired files are cleaned up on a schedule.

That period is a control parameter: changing it is not an ordinary settings update, because extending it directly increases data risk. The cap on how much result data may leave in one go is a control for the same reason.

Even after the file is deleted the trace of the request remains. Who took data, why, under which approval and with which masking decision stays in the audit record. That is what evidence needs; not the result itself.

Writing the Policy Down

A short document is enough, and it should cover four headings.

• A period per record type. Separate periods for the audit trail, script text, query result files and notification records. Tying them all to one period lets the shortest dictate the longest.
• The basis for each period. Which regulation or internal decision each period rests on. A period with no basis will be questioned in an audit.
• The deletion procedure. Who deletes, under which approval and with which checks. If anything is removed from the audit trail, that procedure should include chain verification.
• Exceptions. Retention is suspended while an investigation or litigation is ongoing. Who declared that hold and when it was lifted should be recorded.

Writing this takes a few hours and closes one of the most common audit questions. Left unwritten, the answer is always a rough guess, and that is an admission that the control is not documented.

Frequently Asked Questions

Does the product delete old audit records automatically?

No. There is no automatic purge job and audit records are never deleted on their own. Retention is left to the organisation's database and archive policy. That is deliberate: a product that quietly deletes its own audit records breaks the most basic assumption of an audit trail. Query result files, by contrast, do have a defined retention period and a regular cleanup.

Does deleting old records break the chain?

Yes, and that is expected behaviour. Verification will show a break where the deleted record was. So archiving should be planned, its boundary aligned with an anchor already delivered outside, the chain verified beforehand, and the archiving operation itself recorded in the live chain. Otherwise planned maintenance looks like tampering to an auditor.

How long should we keep the audit trail?

Your regulatory framework sets the period; no software can tell you what it is. What we can say is that volume should not drive the decision: for an organisation raising a thousand requests a year, five years of audit trail is a few hundred thousand rows. Put in the same bag as application logs, the bulky one's cost shortens the small one's retention, and that mistake usually goes unnoticed.

Do we have to keep the full text of scripts?

If you want evidence, yes. One of the most frequent auditor questions is whether the text that ran was the text approved, and it is answered only by comparing the digests of the two. Keeping the digests but not the text means you cannot show where the difference lies. Script text does create volume, but it is an indispensable part of the evidence.