Spotting Agent Jobs That Run Longer Than Usual

A job can succeed every night while taking a little longer each week. Agent jobs that run longer than usual deserve attention before they miss a business deadline. Start with msdb history, then check what is still running now.

Read Job-Level History First

SQL Server Agent writes job and step history to msdb. Use rows where step_id is zero for the job-level outcome. Step rows explain which part ran long. Mix them into a job baseline and you count the same run several times. Filter by job_id and order by instance_id or start date when reviewing a particular job. Check how much history is retained. A short retention window can make a supposed thirty-run baseline impossible.

I start with a job that matters to a deadline, not every job at once. What is the latest acceptable finish time for it? A maintenance task and a morning report have different consequences. Record the owner and schedule alongside the duration.

Decode HHMMSS Correctly

The run_duration column is an integer formatted as HHMMSS, not a number of seconds. Dividing the whole integer by sixty gives nonsense. Extract hours, minutes, and seconds, then convert the parts to a common unit. Hours can exceed 24 for long jobs, so do not format the value as a time of day. The query below returns recent job-level runs and their calculated seconds.

I check one conversion by hand before using it in an alert. A duration of 10203 means one hour, two minutes, and three seconds. That is a format example, not an observed job. The integer has fooled enough dashboards to earn its own line in the runbook.

SELECT TOP (100)
    j.name AS JobName,
    h.run_date,
    h.run_time,
    h.run_status,
    h.run_duration,
    CONVERT(bigint, h.run_duration / 10000) * 3600
      + ((h.run_duration % 10000) / 100) * 60
      + (h.run_duration % 100) AS DurationSeconds
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
  ON j.job_id = h.job_id
WHERE h.step_id = 0
ORDER BY h.instance_id DESC;

Build a Baseline per Job

Compare a job with its own recent history, not with all Agent work on the server. The next query ranks completed job runs and averages up to thirty earlier successful runs for each job. It compares the latest completed run with that baseline and marks a duration above twice the average for review. The multiplier is a starting rule to test, not a universal alert threshold. A short job and a long job can need different absolute tolerances.

I require enough history before trusting an average. If a job ran only a few times, mark the baseline immature. For jobs with strong weekday or month-end patterns, build separate baselines for those schedules. Thirty runs from mixed workloads can hide a predictable slow day.

WITH Runs AS
(
    SELECT
        j.job_id,
        j.name AS JobName,
        h.run_status,
        h.run_date,
        h.run_time,
        CONVERT(bigint, h.run_duration / 10000) * 3600
          + ((h.run_duration % 10000) / 100) * 60
          + (h.run_duration % 100) AS DurationSeconds,
        ROW_NUMBER() OVER
          (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS rn
    FROM msdb.dbo.sysjobhistory AS h
    JOIN msdb.dbo.sysjobs AS j
      ON j.job_id = h.job_id
    WHERE h.step_id = 0
      AND h.run_status IN (0, 1)
),
Baseline AS
(
    SELECT
        job_id,
        COUNT(*) AS SampleRuns,
        AVG(CAST(DurationSeconds AS decimal(18,2))) AS AvgSeconds
    FROM Runs
    WHERE rn BETWEEN 2 AND 31
      AND run_status = 1
    GROUP BY job_id
)
SELECT
    r.JobName,
    r.run_date,
    r.run_time,
    r.run_status,
    r.DurationSeconds,
    b.SampleRuns,
    b.AvgSeconds,
    CASE WHEN b.SampleRuns >= 10
              AND r.DurationSeconds > b.AvgSeconds * 2
         THEN 'Review'
         ELSE 'Within review rule'
    END AS DurationFlag
FROM Runs AS r
LEFT JOIN Baseline AS b
  ON b.job_id = r.job_id
WHERE r.rn = 1
ORDER BY r.JobName;

Do Not Miss a Job Still Running

Job history usually records a completed job only after it finishes. A job stuck right now can be absent from the completed-run query. Use sysjobactivity for the latest Agent session and look for a start time without a stop time. Compare elapsed time with that job’s baseline and the business deadline. Account for Agent jobs that run longer for a legitimate reason during certain maintenance windows.

The live query below lists jobs that the current Agent session reports as running. It does not claim they are blocked or unhealthy. I pair it with job step, wait, and application evidence before intervening. Never stop a job only because it crossed a generic threshold.

SELECT
    j.name AS JobName,
    a.start_execution_date,
    DATEDIFF(second, a.start_execution_date, SYSDATETIME())
        AS ElapsedSeconds
FROM msdb.dbo.sysjobactivity AS a
JOIN msdb.dbo.sysjobs AS j
  ON j.job_id = a.job_id
WHERE a.session_id =
    (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND a.start_execution_date IS NOT NULL
  AND a.stop_execution_date IS NULL
ORDER BY ElapsedSeconds DESC;

Why Agent Jobs Run Longer Over Time

A single large run can come from a temporary lock or an unusual data load. A steady rise over many runs points toward growth, changing plans, or work accumulating in the job. Plot or review the last thirty durations in run order and compare early and late groups. Add processed row counts if the job logs them. A job that slows as its data grows is normal, right up until it nears its deadline.

I ask whether the job’s work increased before tuning the query. A faster plan is useful, but the trend can be caused by a bigger input. If the run duration grows while input stays flat, investigate waits, indexes, and plan changes. Separate volume growth from efficiency loss.

Find the Step That Makes Agent Jobs Run Longer

Once a job is flagged, review step-level history for the same execution. Check the step message, command, start time, and retries. If the job is running, inspect current requests on SQL Server and any external process it started. A job can spend its time in PowerShell, a file transfer, or another server. A quiet SQL session does not clear it.

I keep the investigation tied to the job’s run instance and start time. Similar job names or overlapping retries can confuse a timeline. Do not replace an evidence trail with a guess about the last query changed. The slow step tells you where to look next.

Alert on Agent Jobs That Run Longer Before the Deadline

Use a baseline and an absolute business deadline together. An alert that fires after the deadline is useful for diagnosis but late for prevention. Trigger a review while there is still time to recover. Send one alert per run, not a stream of repeats. Route it to the job owner with the elapsed time, the recent baseline, and the schedule. Keep failures as a separate alert path.

I review the threshold after a few real notifications. Too many harmless pages teach people to ignore the next one. Too few leave a growing duration invisible. The goal is an early question backed by your own msdb history. A fixed duration copied from another instance tells you nothing.

Keep the Baseline Honest

Agent history can be purged, jobs can be renamed, and job logic can change. Rebuild or annotate the baseline after a meaningful release. A duration comparison across two different implementations can be misleading. Preserve enough history for the last thirty runs and monitor the retention job itself. If the sample is too small, say so rather than inventing a normal value.

A useful duration report explains what was compared and why the job was flagged. I keep the formula visible in the query and the response process short. Then a gradually growing job has a chance to be fixed before its next deadline becomes a surprise.

Related reading on this blog: T-SQL Script to Check SQL Server Job History and Retrieve Information of SQL Server Agent Jobs.

A slow job is not a failure yet, it is a warning you still have time to act on.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.