Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Current »

On large instances lots of audit logs can build up over time. We are tracking the ability to expire items on  AUT-333 - Getting issue details... STATUS . Until this is implemented you can manually delete items from the database.

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:

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:

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.

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";


None of this information is cached so there is no need to restart JIRA or the add-on.


  • No labels