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

1 vote

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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,338 views 14 20
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot