It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Identify and list empty sprints

I am working to clean up our JIRA instance (Server 8.4.2) and have found that at one point before I was given this task that there were multiple sprints created with the same name (over 300)... Doing the whole make it active and add a dummy issue to it to delete would be okay if it was only a handful but the sheer number is too much for manual method.

So while it would be easy to go into the database and just delete them all, I want to make sure that none of them have issues associated to any one of these sprints.

Which is why I am looking for a way via SQL , JQL or even Rest call to see list sprints that have no issues linked to them.

In the database I know that "AO_60DB71_SPRINT" table contains the sprint information and I can see the multiple versions of the same name with different ID numbers.   However, I have not seen how this table is linked to what would be considered a issue table where it would link to a sprint ID.   If I can get that then I can see that a particular sprint and its ID is not connected to an issue and that would allow it to be deleted with no worries.

 

 

 

 

 

2 answers

If you want to query on the database join the table you found with customfield row with name `Sprint`, and customfieldvalue, and jiraissue.

It's a bit of a pain so I would not do it like that. I also would not recommend writing to the database with SQL, unless you're very confident and you have thoroughly tested first on a development instance. You would probably need to restart jira afterwards so various caches can be reset. 

I have written the following ScriptRunner script (you could just use an eval licence to run this as a once-off). Go to Admin -> Script Console, paste in the following, click Run. Go to the Logs tab and verify it looks correct, then uncomment the marked couple of lines, which will actually delete the sprints.

https://gist.github.com/jechlin/d95251edb3e97d9ae6b1a35d4f3dc2d0

This is is a start for normal sprints that have no issues associated to it.   The problem that I have is that I have 300+ sprints with same name but different sprintID numbers.   The original sprint in this group has issues associated to it but the other ones have nothing.  So what happens is when you start typing the sprint name in a search or filter then you are given a list showing all 300+ sprints and you have to be able to know which one to pick to get the correct information.

 

I will play a bit with this script and see what I can come up with.  It is at least a start.

This will just delete every sprint that doesn't have any issues. I don't really see why that won't solve the problem. If you had 300 sprints with the same name that contain issues it wouldn't help, but that doesn't seem to be the issue here.

Right.   I have 300+ sprints with the same name but different sprint ID numbers.  The issues are only associated to one of the Sprint ID numbers the rest of the sprints are just empty.

So any time you start a search and type in the sprint name you are presented with all 300+ sprints of the same name.  From that list you have to figure out which one is the one that the issues are actually linked to for the list of issues to show.  If you choose one that is not the right sprint id then none of the issues show up and the sprint shows as being empty.

 

Here is example when doing a search.   As you can see the KYC_04.04.290.000 is listed multiple times with different ID numbers.   Right now the issues might be associated to the sprint with the ID number of 12002 so if you select that one you see the list of issues.  But you select any of the others it shows the sprint as being empty.  The ones like that are what I am trying to get rid of from the system to make it easier for the users to do searches and create dashboards.

 

I am still trying to research what caused the multiple sprints to be created.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Agile

Overview of Advanced Roadmaps UI + Get started with Automation for Jira - Demo Den September 2020

Deliver better and faster value to your customers with the Jira automation platform. Watch this Demo Den series below to learn how to use the new interface and set up automation rules to help your te...

1,127 views 2 5
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you