Reading SQL Agent history like a DBA: queries for failures, durations, trends and slow steps

SQL Agent history lives in msdb.dbo.sysjobhistory. The format is awkward: dates and times are integers, durations are HHMMSS packed into an int, and job outcome and step outcome share one table. Once you know the layout, the useful queries are short.

#How the table works

Two helper expressions are used throughout:

-- start time as datetime (documented-columns version of msdb.dbo.agent_datetime)
DATEADD(second, (h.run_time / 10000) * 3600 + (h.run_time / 100 % 100) * 60 + h.run_time % 100,
        CONVERT(datetime, CONVERT(char(8), h.run_date)))

-- duration in seconds
(h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100

#What failed in the last 24 hours, and why

WITH h AS (
  SELECT j.name AS job_name, h.step_id, h.step_name, h.run_status, h.message, h.instance_id,
         DATEADD(second, (h.run_time / 10000) * 3600 + (h.run_time / 100 % 100) * 60 + h.run_time % 100,
                 CONVERT(datetime, CONVERT(char(8), h.run_date))) AS started
  FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id)
SELECT job_name, started, step_id, step_name, LEFT(message, 400) AS error_text
FROM h
WHERE run_status = 0 AND step_id > 0 AND started >= DATEADD(hour, -24, GETDATE())
ORDER BY started DESC;

Filtering on step_id > 0 shows which step failed and its error. The job-level row (step_id = 0) only says "The job failed. The Job was invoked by Schedule... The last step to run was step 2".

#Duration per job: average, worst and last run

WITH runs AS (
  SELECT j.name, h.run_date,
         (h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100 AS secs,
         ROW_NUMBER() OVER (PARTITION BY h.job_id ORDER BY h.instance_id DESC) AS rn
  FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
  WHERE h.step_id = 0 AND h.run_status = 1)
SELECT name,
       COUNT(*)                                   AS runs,
       AVG(secs) / 60.0                           AS avg_min,
       MAX(secs) / 60.0                           AS max_min,
       MAX(CASE WHEN rn = 1 THEN secs END) / 60.0 AS last_min
FROM runs GROUP BY name ORDER BY avg_min DESC;

#Jobs getting slower (last run versus 30-day average)

WITH runs AS (
  SELECT j.name, h.instance_id,
         CONVERT(date, CONVERT(char(8), h.run_date)) AS run_day,
         (h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100 AS secs
  FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
  WHERE h.step_id = 0 AND h.run_status = 1),
stats AS (
  SELECT name, AVG(CAST(secs AS float)) AS avg_secs, STDEV(secs) AS sd_secs
  FROM runs WHERE run_day >= DATEADD(day, -30, CAST(GETDATE() AS date)) GROUP BY name),
last_run AS (
  SELECT name, secs, ROW_NUMBER() OVER (PARTITION BY name ORDER BY instance_id DESC) AS rn FROM runs)
SELECT l.name, l.secs / 60.0 AS last_min, s.avg_secs / 60.0 AS avg_30d_min,
       CAST(l.secs / NULLIF(s.avg_secs, 0) AS decimal(6, 2)) AS ratio
FROM last_run l JOIN stats s ON s.name = l.name
WHERE l.rn = 1 AND l.secs > s.avg_secs + 2 * ISNULL(s.sd_secs, 0) AND l.secs > 300
ORDER BY ratio DESC;

This flags jobs whose last run took more than two standard deviations longer than usual (ignoring jobs under 5 minutes). It's a good candidate for a daily report.

#Slowest steps inside a job

SELECT h.step_id, h.step_name, COUNT(*) AS runs,
       AVG((h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100) / 60.0 AS avg_min
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name = N'Finance - ETL - Load GL' AND h.step_id > 0 AND h.run_status = 1
GROUP BY h.step_id, h.step_name ORDER BY h.step_id;

#What is running right now

SELECT j.name, a.start_execution_date,
       DATEDIFF(minute, a.start_execution_date, GETDATE()) AS running_min,
       ISNULL(a.last_executed_step_id, 0) + 1 AS current_step
FROM msdb.dbo.sysjobactivity a JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)   -- current Agent session only
  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL
ORDER BY a.start_execution_date;

Filtering on the latest syssessions row matters: sysjobactivity keeps rows from earlier Agent sessions, and jobs that were running when Agent stopped look "still running" forever.

#Success rate per job (last 30 days)

SELECT j.name,
       SUM(CASE WHEN h.run_status = 1 THEN 1 ELSE 0 END) AS ok,
       SUM(CASE WHEN h.run_status = 0 THEN 1 ELSE 0 END) AS failed,
       CAST(100.0 * SUM(CASE WHEN h.run_status = 1 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(5, 1)) AS success_pct
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.step_id = 0 AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))
GROUP BY j.name HAVING SUM(CASE WHEN h.run_status = 0 THEN 1 ELSE 0 END) > 0
ORDER BY success_pct;

#When history is missing

If a job ran but has no rows, history was probably purged by the row limits. The defaults are 1,000 rows in total and 100 per job, so a busy server keeps only a few days. Raise the limits, or archive history (see the history retention post).

Tip

Messages in sysjobhistory are truncated. For long error output, set the step's output file or "Log to table" option, and read msdb.dbo.sysjobstepslogs (via sp_help_jobsteplog) or the file.