Audit trail retention: the audit log that deleted the answer on day 91

Your auditor wants the history of one supplier's bank account since January. You open the audit log, filter on the supplier, and the first entry is dated June. Nobody deleted anything by hand. A SQL Server Agent job did it, once a minute, as configured, because the retention setting said 90 days and nobody had ever opened that screen.

That 90 is our default. Every new Primentra install keeps audit history for 90 days until somebody changes the number. For an error log, 90 days is fine. For the change history of the data your finance team closes the books on, it is too short, and the first person to notice is usually the auditor.

What the cleanup deletes on day 91

The audit log is one table, AuditLog, and everything that answers "who changed this?" lives in it. An entry that passes the cutoff takes all of this with it:

• the old and the new value of every field in every edit
• the full snapshot of every deleted row
• sign-ins, failed sign-ins and account lockouts
• every permission change, with the role and the flags that moved

The deleted rows hurt most. Primentra deletes rows for real and writes the whole row into the audit log first. That snapshot is what you re-import from when a delete was a mistake. Once it ages out, your database backups are the last place the row exists.

The history in the grid disappears with it. The audit panel on a record reads the same table, so a steward who opens a supplier sees 90 days of changes and nothing to say there were more.

Approval requests run on a clock of their own. Primentra keeps a resolved request for 365 days, counted from the day somebody submitted it, and the cleanup never touches a pending one. So between day 91 and day 365 you can still open the approval request for a change, while the audit entry for that same change is gone. You end up with two records of one event and two expiry dates.

Disk space is a weak reason to keep less

The usual argument for a short window is storage. Measure it before you accept it. On our own development database, an edit entry carries about 280 bytes of change data. A delete carries about 1,500, because it holds the whole row.

Now take a busy install. Say your stewards and integrations change 5,000 rows a day, at 500 bytes each. That comes to 2.5 MB a day, about 0.9 GB a year, and about 6.4 GB after seven years, before indexes. The audit log sits in a table of its own, and the queries that load the grid never read it. On a SQL Server that already holds your master data, that is a small table.

The real reason to keep less: personal data

One argument for a shorter window does hold up. The audit log stores old values, and old values include a contact's previous email address, a former employee's phone number, and the name somebody asked you to erase. Article 5(1)(e) of the GDPR says you may keep personal data in identifiable form for no longer than the purpose needs. A history that keeps a person's old details for 27 years needs a purpose that lasts 27 years.

So the number has a ceiling as well as a floor. Keep history as long as the longest period someone will test, and deal with erasure requests on their own. We wrote about what an erasure request does to the audit trail in a separate post.

How to pick the number

1. Ask whoever signs off your audit which period they test. Finance tends to have that number in writing already. Do not take it from a vendor default, ours included.
2. Take the longest period across everything you keep in Primentra. Audit retention is one setting for the whole installation, not one per entity, so the entity with the longest obligation decides it.
3. Open Settings → General Settings → Retention and type the number in days. The preset buttons stop at two years, but the field accepts up to 9,999. For seven years, type 2557: that covers whichever leap days fall inside the period.
4. Read the orange banner under the field. It names a date and says all entries before it will be removed. If your auditor could ask about anything older than that date, the number is still too small.
5. Set the approval clock separately. It starts at 365 days, and it is the only card with a Never option.

Lowering the number deletes history within a minute

You cannot undo a shorter window. The cleanup job runs every 60 seconds and reads the current setting on every run, so a change applies on the next run with no restart. Cut the audit window from two years to 90 days, click Save settings, and about 21 months of history is gone a minute later.

A longer window does not bring anything back either. If you plan to shorten it, export first. The audit log exports to JSON, CSV or XLSX, filtered by date, with every field-level change in the file.

Check that the job runs at all

The opposite failure makes no noise. Retention only happens while the SQL Server Agent job Primentra_Scheduler exists and runs. Without it, nothing gets deleted, the log grows without limit, and the Retention tab describes a policy that nobody enforces. SQL Server Express has no Agent, so an Express install needs the Windows Task Scheduler alternative.

Primentra looks for the job itself and reports it under Settings → General Settings → Database. To check by hand, connect as a sysadmin and run:

SELECT name, enabled
FROM   msdb.dbo.sysjobs
WHERE  name = 'Primentra_Scheduler';

No row means no job, and no retention. Each cleanup run that deletes something writes one line to SchedulerLog, so this query shows what the cleanup removed and when:

SELECT EventTime, Message
FROM   SchedulerLog
WHERE  EventType = 'cleanup'
ORDER BY EventTime DESC;

An auditor may ask whether the retention policy on paper is the one running in production, and that query answers it with dates.

Common questions