What jobs do we have and what do they do? A one-query SQL Agent job inventory

#Why

When you inherit a server, the first question is "what runs here?". SSMS shows one job at a time. This query gives one row per job with everything you need to triage.

#The inventory query

USE msdb;
WITH last_run AS (
  SELECT job_id,
         MAX(instance_id) AS last_instance
  FROM dbo.sysjobhistory WHERE step_id = 0 GROUP BY job_id
), steps AS (
  SELECT job_id, COUNT(*) AS step_count,
         STRING_AGG(subsystem, ',') WITHIN GROUP (ORDER BY step_id) AS subsystems,
         MAX(CASE WHEN proxy_id IS NOT NULL THEN 1 ELSE 0 END) AS uses_proxy
  FROM dbo.sysjobsteps GROUP BY job_id
), scheds AS (
  SELECT js.job_id,
         STRING_AGG(s.name + CASE WHEN s.enabled = 0 THEN ' (disabled)' ELSE '' END, '; ') AS schedules
  FROM dbo.sysjobschedules js JOIN dbo.sysschedules s ON s.schedule_id = js.schedule_id
  GROUP BY js.job_id
)
SELECT j.name                              AS job_name,
       c.name                              AS category,
       SUSER_SNAME(j.owner_sid)            AS owner,
       j.enabled,
       ISNULL(sc.schedules, '(none)')      AS schedules,
       st.step_count, st.subsystems, st.uses_proxy,
       o.name                              AS email_operator,
       CASE j.notify_level_email WHEN 0 THEN 'never' WHEN 1 THEN 'success'
            WHEN 2 THEN 'failure' WHEN 3 THEN 'completion' END AS email_when,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS last_run,
       CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' END AS last_outcome,
       j.date_created, j.date_modified,
       j.description
FROM dbo.sysjobs j
JOIN dbo.syscategories c       ON c.category_id = j.category_id
LEFT JOIN steps st             ON st.job_id = j.job_id
LEFT JOIN scheds sc            ON sc.job_id = j.job_id
LEFT JOIN dbo.sysoperators o   ON o.id = j.notify_email_operator_id
LEFT JOIN last_run lr          ON lr.job_id = j.job_id
LEFT JOIN dbo.sysjobhistory h  ON h.instance_id = lr.last_instance
ORDER BY c.name, j.name;

STRING_AGG needs SQL Server 2017 or later. On 2016, use FOR XML PATH concatenation.

#What to look for

#Export it

Invoke-Sqlcmd -ServerInstance SQL01 -Database msdb -InputFile .\job-inventory.sql |
  Export-Csv .\SQL01-jobs.csv -NoTypeInformation

For many instances, see the PowerShell inventory post (dbatools Get-DbaAgentJob).

#Step-level detail

When you need the commands too:

SELECT j.name, s.step_id, s.step_name, s.subsystem, s.database_name,
       p.name AS proxy, s.on_success_action, s.on_fail_action, s.retry_attempts,
       LEFT(s.command, 400) AS command_start
FROM msdb.dbo.sysjobsteps s
JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
LEFT JOIN msdb.dbo.sysproxies p ON p.proxy_id = s.proxy_id
ORDER BY j.name, s.step_id;

Tip

Save the inventory monthly. Comparing two snapshots is the quickest way to spot new, removed or changed jobs.

#Prevent surprises

• Require a description and category for every new job.
• Review owners and notification settings quarterly.
• Keep the inventory with your server documentation.