Summary

This article offers ways to find which SLA fields are in use or unused in Jira Service Management (formerly  called Jira Service Desk).

Environment

All versions of Jira Service Desk/Management from 3.0.0.

Solution

We can assess the usage of SLA fields in two ways: JQL in one-by-one or through SQL.

By "SLA in use", we mean "if any existing issue has any value for that SLA field".

JQL one-by-one

For each SLA custom field, you can execute this JQL:

"My SLA 1" = running() or "My SLA 1" != running()

If no issues are listed, the SLAs are not in use.

SQL to find SLA in use

This SQL query lists all fields that are or once were in use (i.e. have any value):

select distinct cf.cfname
from customfield cf
join customfieldvalue cfv on cfv.customfield = cf.id
where cf.customfieldtypekey = 'com.atlassian.servicedesk:sd-sla-field'
and cfv.textvalue like '%"events":[{%';

SQL to find unused SLA

Using the query above as an exclusion, we can list all SLA fields that aren't and have never been used:

select distinct cf.cfname
from customfield cf
where cf.customfieldtypekey = 'com.atlassian.servicedesk:sd-sla-field'
and cf.id not in (
  select cf2.id 
  from customfield cf2, customfieldvalue cfv 
  where cf2.customfieldtypekey = 'com.atlassian.servicedesk:sd-sla-field'
  and cfv.customfield = cf2.id
  and cfv.textvalue like '%"events":[{%'
);

If a field was only used by issues that were deleted, it'll show up as unused.

Deleting the unused SLA fields

These locked custom fields can be deleted through Jira itself only once no project is using them — even if no issue is using them.

You can go project-by-project in Jira (and go to their SLA configuration page via Project Settings > SLAs) or use this SLA query for each SLA to filter the projects that need the SLA removed:

SELECT sla."NAME", p.pname, p.pkey
FROM "AO_54307E_TIMEMETRIC" sla
join "AO_54307E_SERVICEDESK" sd on sd."ID" = sla."SERVICE_DESK_ID"
join project p on p.id = sd."PROJECT_ID"
where sla."NAME" = 'My SLA 1'; -- replace here for your SLA's custom field name

You can find below an example of output. In this example, we can see that 2 projects are using the SLA called "Test SLA", which are the projects with key SDBASIC and ANOTHERSD:

|NAME    |pname                |pkey     |
|--------|---------------------|---------|
|Test SLA|Some basic SD project|SDBASIC  |
|Test SLA|Another SD project   |ANOTHERSD|

If the SQL query returns any project, you will need to go to each project's admin page, select SLAs and then Delete SLA.

Once this SLA has been deleted from each project, follow our documentation on Managing SLA data to clean up (delete) the unused fields.

 Note about the SLA clean-up functionality:

• from Jira Service Management 3.0.0 up to 4.21.x, the SLA clean up can be done from ⚙ > Applications > JIRA SERVICE DESK > Configuration > SLA Names
  
  
• from Jira Service Management 4.22.0, the SLA clean up can be done from ⚙ > Applications > JIRA SERVICE MANAGEMENT > SLA Configuration > General SLA Settings > Configure
  
  
• from Jira Service Management 5.0.0, the SLA clean up can be done from ⚙ > Applications > JIRA SERVICE MANAGEMENT > SLA Configuration, in the section SLA Custom Fields. If the number of "custom fields not in use" does not equal 0, the Clean Up button will be clickable and you will be able to clean up unused SLA fields as shown below: