Expire audit log items
On large instances, audit logs can build up over time, impacting your Jira database performance and clogging up your disk space. This guide explains how you can regularly delete those old audit log items you no longer need.
Configuring audit log expiry
If you're running Automation for Jira 3.12+, then audit log expiry can now be configured by global administrators. Head to the Global configuration section, then enable audit log expiry by sliding the audit log expiry period to your preferred value.
Save Save and you're done. Audit logs now expire on that schedule.
This operation can be expensive and cause extra database load if you have a lot of audit log items. Ensure that you pick a suitable time and set maximum run time limits so the expiry only happens in the least busy periods for your Jira instance (e.g. on the weekends).
If you have a large backlog of audit items, you may want to delete them all in bulk first using the database queries below during a scheduled downtime, before configuring the automatic audit log expiry in global configuration.
Expiring items directly in the database
This is a destructive process and, if done incorrectly, can cause data loss or corruption. We recommend doing a backup in case you need to roll back.
This procedure should only be necessary if you're on an old version of Automation for Jira or have a lot of audit items that you want to delete in bulk during scheduled downtime.
You do this in 3 steps: Identify the tables, then check the number of items, and, lastly, delete them. None of this information is cached, so there is no need to restart Jira or the add-on at the end.
First, check the counts on the tables you want:
SELECT count(*) FROM "AO_589059_AUDIT_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_ASC_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_CGE_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_COMP_CGE";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_PROJECT";Next, pick a date and see how many items will be deleted:
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_ASC_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_CGE_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_COMP_CGE"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_PROJECT"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508';This would be a good time to do a backup.
Now delete the items.
DELETE FROM "AO_589059_AUDIT_ITEM_ASC_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_CGE_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_COMP_CGE"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_PROJECT"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508';Verify the new counts on the tables:
SELECT count(*) FROM "AO_589059_AUDIT_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_ASC_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_CGE_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_COMP_CGE";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_PROJECT";