How to delete archived issues

Summary

If incase, environment has large number of archived issues and if we are sure, this is not required in future, those can be removed using the steps mentioned in this article.

Solution

• Execute the SQL query from the database that will return all the issue keys and summary of archived issues:

  1 2 3 select ( p.pkey || '-' || ji.issuenum ) as "issue_key" from jiraissue ji join project p on p.id = ji.project where ji.archived = 'Y';

• Considering the output mentioned below:

  1 2 3 4 issue_key ----------- PROJ-1 (1 row)

• User can export the output as a CSV file and then iterate through those values in your PowerShell.

• Here is how you can create a bash script that can be execute directly straight from Linux server:

Here are the steps:

1. Create a .sh file with this content:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 #!/bin/bash echo "What's your Jira URL? Provide with the scheme, e.g. https://myjira.com" read jiraurl echo "What's your Jira username?" read username echo "What's your password?" read password cat issuelist.csv | while read line do echo "Restoring issue $line" curl -u $username:$password \ -H "Content-Type:application/json" \ -X PUT \ -k $jiraurl/rest/api/2/issue/$line/restore sleep 0.1 echo "Deleting issue $line" curl -u $username:$password \ -H "Content-Type:application/json" \ -X DELETE \ -k $jiraurl/rest/api/2/issue/$line sleep 0.1 done

2. Obtain a list of all archived issues using the SQL below

   1 2 3 select ( p.pkey || '-' || ji.issuenum ) as "issue_key" from jiraissue ji join project p on p.id = ji.project where ji.archived = 'Y';

3. You can customise the query further, for example to query for archived issues in project PROJ the query would be:

   1 2 3 select ( p.pkey || '-' || ji.issuenum ) as "issue_key" from jiraissue ji join project p on p.id = ji.project where ji.archived = 'Y' and p.pkey = 'PROJ';

4. Put the output of the query into a file called ‘issuelist.csv’ in the same directory that you created the .sh file, with one issue key per line – the same way that the query will provide you

   • 1 2 3 4 5 PROJ-10 PROJ-16 PROJ-15 PROJ-14 PROJ-17

5. Run the script and provide your Jira baseurl, your username and password:

   1 2 3 4 5 $ sh delete-archived-issues.sh What's your Jira URL? Provide with the scheme, e.g. https://myjira.com https://10.125.91.129 What's your Jira username? USERNAME What's your password? mypassword

6. The script will iterate through each line, restore and then delete the issues. We need to restore the issues first because you can’t delete an archived issue directly:

   1 2 3 4 5 6 7 8 9 10 11 12 13 $ sh delete-archived-issues.sh What's your Jira URL? Provide with the scheme, e.g. https://myjira.com https://10.125.91.129 What's your Jira username? USERNAME What's your password? PASSWORD Restoring issue PROJ-10 Deleting issue PROJ-10 Restoring issue PROJ-16 Deleting issue PROJ-16 Restoring issue PROJ-15 Deleting issue PROJ-15 Restoring issue PROJ-14 Deleting issue PROJ-14

There's a sleep of 1/10 of a second just to prevent a heavy load towards your Jira server. Please also remember to take a backup first in case any incorrect issues are deleted by mistakenly (e.g wrong SQL query)

Related info

The article How to speed up Jira Data Center project delete by deleting data ahead of time also present alternatives for deleting issue data directly from database.