Clean up extra Service Management data in the db
Symptoms
The rate of growth of data in your DB has drastically increased. You find this coming from the changeitem
table. You use the Automation plugin in your instance.
Diagnosis
The query will tell you the amount of data being created in the changeitem
table by month. You could adjust WHERE clause and set "...and ci.field NOT in
..." to query for the data being created by everything which is not Service Management. If you see a huge spike like for some of month, then you are potentially affected by this issue.
select
date_trunc('month', cg.created),
sum(coalesce(pg_column_size(ci.oldstring),0))
+ sum(coalesce(pg_column_size(ci.newstring),0))
as total
from
changegroup as cg,
changeitem as ci
where
ci.groupid = cg.id
and ci.field in (
select
cfname
from
customfield
where
customfieldtypekey like 'com.atlassian.servicedesk%'
)
group by
date_trunc('month', cg.created)
order by
date_trunc('month', cg.created)
Queries are only written for PostgreSQL. If you use a different SQL server, you will need to adjust the queries for your db.
Cause
This is a bug in the combination of how the Automation plugin and Jira Service Management work together. Issue was tracked at: - JSD-1060Getting issue details... STATUS
Workaround
You can delete the bad data from the DB. We recommend performing a VACUUM / ANALYZE after the deletion completes.
delete from changegroup where id in (select id from changegroup where
id in (select groupid from changeitem where field in
(select cfname from customfield
where customfieldtypekey like 'com.atlassian.servicedesk%'))
except
(select groupid from changeitem where field not in
(select cfname from customfield
where customfieldtypekey like 'com.atlassian.servicedesk%')));
delete from changeitem
where field in
(select cfname from customfield
where customfieldtypekey like 'com.atlassian.servicedesk%');
Queries are only written for PostgreSQL. If you use a different SQL server, you will need to adjust the queries for your db.
Resolution
Per - JSD-1060Getting issue details... STATUS you need to upgrade to Jira Service Management 2.1+ to fix this issue.