How can i remove items from the changeitem table.

My changeitem table now consumes over 50 percent of my database and it is growing, currently it is 19g on a 30g database with 500 issues. Most of the entries are due builds and interaction with SCM systems. What can i do

1) remove items periodically from change table.

2) limit the number of changeitems kept per issue.

1 answer

1 accepted

This widget could not be displayed.

Removing the data destroys the information about changes, so you need to think very carefully before you do this.

The only way to do it without coding is to use SQL directly to kill the lines you decide you don't want. As you specify builds and SCM interactions, I'd attack them first. Identify the patterns, work out a decent rule for them, and remove the lines in SQL. However, you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems.

When you run the SQL for the two tables, you absolutely MUST backup the database, have Jira offline, and reindex as soon as you restart Jira.

As for point 2 - you can't. There's no limitations in Jira, it just keeps tracking changes.

Thanks, you answered my question.

 

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

those who interested, PostgreSQL snippet to delete change groups that don't have change items

-- WARNING! DO NOT APPLY FOR PROD INSTANCE!

DELETE
FROM changegroup
WHERE ID = any (
  ARRAY(
    SELECT G.id
    FROM changegroup G
    LEFT JOIN changeitem I ON I.groupid = G.id
    WHERE I.groupid IS NULL
  )
);

 

DO NOT DO THAT.

There are items in changegroup that have no items for very good reasons.  You are damaging your data.

Please, restore your data from the backup you took before doing this and don't ever do it again.

There are items in changegroup that have no items for very good reasons. 

Nic Brough, any example of such entries? Checked on a large enough prod JIRA instance and didn't find any Change groups without Change items.

Yes.

Why are you removing them?

Why are you removing them?

In my case it was for partly anonymized staging (copy of prod) instance.

But generally I added the snippet for those who trying to do this:

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

Ok, you're going about "anonymising" it in a broken way.  You should never delete this stuff, just change it so it's not identifiable.

Please, step away from the database, you could be doing all sorts of damage by doing things you don't understand.

For anonymisation, do it the correct way - see https://confluence.atlassian.com/jira/anonymising-jira-data-139008.html

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted yesterday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

81 views 1 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you