On large instances lots of audit logs can build up over time. We are tracking the ability to expire items on
| Jira Legacy |
|---|
| server | JIRA (codebarrel.atlassian.net) |
|---|
| serverId | 61a6faa1-7dce-3128-9712-97252610cb36 |
|---|
| key | AUT-333 |
|---|
|
. Until this is implemented you can manually delete items from the database.| Warning |
|---|
This is a destructive process and if done incorrectly, could cause data loss or corruption. It is recommended that a backup is done before incase you need to roll back. |
Step-by-step guide
We will do this in 3 steps. Identify the tables, check the number of items and then delete them.
First of all check the counts on the tables you want:
| Code Block |
|---|
|
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 we pick and date and see how many items will be deleted:
| Code Block |
|---|
|
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'; |
Next we delete the items. This would be a good time to do a backup.
| Code Block |
|---|
|
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:
| Code Block |
|---|
|
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"; |
None of this information is cached so there is no need to restart JIRA or the add-on.
Related articles
...
| Info |
|---|
Our documentation has recently move to a new location! Please head on over to: https://docs.codebarrel.io/automation/kb/expiring-audit-log-items.html |