Identify and list empty sprints

[Alex Cumberland]

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.

[Alex Cumberland]

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.

[Jamie Echlin _ScriptRunner - The Adaptavist Group_]

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.

[Alex Cumberland]

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.

[Alex Cumberland]

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.

[Jamie Echlin _ScriptRunner - The Adaptavist Group_]

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