Agent Jobs Running Longer Than Usual: Finding Them in Job History

The schedule started the job, but its normal finishing time has passed. Find runs lasting longer than usual by comparing current activity with a clearly defined history baseline.

Define What Longer Than Usual Means

Agent history contains step records and a job-summary record. Use step_id zero for completed job totals, then choose the outcomes that fit the question. Successful runs provide a useful normal-duration baseline, while failures and canceled runs need separate review. Combining all of them can make a short failed job look like normal completion.

Choose a representative history window. A recent deployment, data-volume change, or monthly schedule can create several distinct duration populations. The average across all retained history is not automatically the normal duration for today's workload. I define the comparable run set before writing the alert threshold.

Record history coverage as well. Agent retention can remove older rows, and a newly created job can have too few successful runs. An unavailable baseline should be reported as insufficient evidence rather than a zero-second expectation. A fresh job does not become late at its first breath.

Decode HHMMSS as a Duration

run_duration stores hours, minutes, and seconds as an integer format. It is not a number of seconds, and hours can exceed twenty-three. Decode it arithmetically before averaging or comparing values. Keep large totals in bigint or an appropriate decimal type.

SELECT j.name AS JobName,h.instance_id,h.run_status,h.run_date,h.run_time,
       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;

Do not cast the value to a time-of-day type, which loses the meaning of a multi-day duration. A run date and start time describe when execution began; run_duration describes elapsed work reported for the completed run. Keep those concepts separate in the report.

Inspect unusual values and outcome messages before treating an outlier as a stable performance measurement. A retry path, schedule branch, or changed input can explain the duration. Step-level history can identify where time accumulated, while the summary supplies the completed job's overall outcome.

Calculate Average and Maximum for Comparable Successes

The following example uses successful summary rows from a thirty-day input window. That window is a policy choice for the example, not a required retention or universal baseline. It reports the number of observed runs alongside average and maximum duration.

WITH SuccessfulRuns AS
(
    SELECT h.job_id,
           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
    WHERE h.step_id=0 AND h.run_status=1
      AND h.run_date>=CONVERT(int,CONVERT(char(8),DATEADD(day,-30,GETDATE()),112))
)
SELECT j.job_id,j.name AS JobName,COUNT_BIG(*) AS BaselineRuns,
       AVG(CONVERT(decimal(18,2),r.DurationSeconds)) AS AverageSeconds,
       MAX(r.DurationSeconds) AS MaximumSeconds
FROM SuccessfulRuns AS r
JOIN msdb.dbo.sysjobs AS j ON j.job_id=r.job_id
GROUP BY j.job_id,j.name;

The maximum helps explain how variable the successful population already is. A mean dominated by one unusually long run can conceal a meaningful overrun. Review the distribution and step history for important jobs. Different schedules or input sizes can justify separate baselines rather than one average for every execution.

Job names can change, so retain job_id as the identity within the instance. Recreated jobs receive a different identity and need a reviewed baseline transition. Preserve capture time and the history filter with any exported comparison. An average without its population is an unexplained number.

Inspect Activity From the Current Agent Session

Select the latest recorded Agent session and then inspect its activity rows. An older session can leave a start time without a stop after an interruption. Confirm that Agent itself is running before interpreting an apparently active row as current work.

DECLARE @AgentSession int=(SELECT TOP (1) session_id
    FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC,session_id DESC);
SELECT j.job_id,j.name AS JobName,a.start_execution_date,
       a.last_executed_step_id,a.last_executed_step_date,
       DATEDIFF_BIG(second,a.start_execution_date,SYSDATETIME()) AS CurrentSeconds
FROM msdb.dbo.sysjobactivity AS a
JOIN msdb.dbo.sysjobs AS j ON j.job_id=a.job_id
WHERE a.session_id=@AgentSession
  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL;
SELECT servicename,status_desc,last_startup_time
FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server Agent%';

The last executed step fields do not guarantee that a particular next step is currently running. Branching and retries can change the flow. Use current session and request evidence for the suspected step when needed. History also has separate permissions and visibility boundaries, so verify the diagnostic identity's scope.

These msdb timestamps are local server time. Clock changes and daylight-saving transitions can complicate an elapsed-time comparison made from wall-clock timestamps. Keep that limitation visible and use an approved monotonic or UTC-aware monitoring design when precise overrun timing is essential.

Flag Jobs Running Longer Than Usual

A simple example alert uses twice the average successful duration, a minimum baseline population, and a minimum elapsed duration. Those are explicit policy inputs, not measured facts about your jobs. Adjust them according to the accepted operating requirement.

DECLARE @AgentSession int=(SELECT TOP (1) session_id
    FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC,session_id DESC);
WITH Durations AS
(
    SELECT job_id,CONVERT(bigint,run_duration/10000)*3600
           +(run_duration%10000)/100*60+run_duration%100 AS DurationSeconds
    FROM msdb.dbo.sysjobhistory
    WHERE step_id=0 AND run_status=1
      AND run_date>=CONVERT(int,CONVERT(char(8),DATEADD(day,-30,GETDATE()),112))
), Baseline AS
(
    SELECT job_id,COUNT_BIG(*) AS SampleRuns,
           AVG(CONVERT(decimal(18,2),DurationSeconds)) AS AverageSeconds
    FROM Durations GROUP BY job_id
)
SELECT j.job_id,j.name AS JobName,a.start_execution_date,
       b.SampleRuns,b.AverageSeconds,e.CurrentSeconds
FROM msdb.dbo.sysjobactivity AS a
JOIN msdb.dbo.sysjobs AS j ON j.job_id=a.job_id
JOIN Baseline AS b ON b.job_id=a.job_id
CROSS APPLY (SELECT DATEDIFF_BIG(second,a.start_execution_date,SYSDATETIME()) AS CurrentSeconds) AS e
WHERE a.session_id=@AgentSession
  AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL
  AND b.SampleRuns>=5 AND e.CurrentSeconds>=300
  AND e.CurrentSeconds>2*b.AverageSeconds;

Run the comparison through an approved monitor and deduplicate notifications by job identity and run start. Include the baseline window, elapsed time, and last known activity in the message. Escalate repeated overruns deliberately rather than sending the same notification every polling interval. Reporting a candidate does not authorize canceling the job.

Which step is responsible for the extra time? Review blocking, plan changes, storage waits, external dependencies, and larger inputs. I compare the active operation with its expected work before recommending an intervention. A job running longer than usual can be making valid progress on an unusually large task.

Follow a Run Longer Than Usual to Its Outcome

Capture the observation time and the evidence that the job is still progressing. Some steps expose row counts, files processed, or application checkpoints through their own approved operational logs. Compare that evidence with the remaining work before deciding whether the run is stalled. An elapsed duration alone does not distinguish healthy progress from repeated retries or a blocked request.

After the run ends, join the incident record to its completed summary and step outcomes. Preserve the cause and whether the job produced its required result. A run that eventually succeeds can still reveal a capacity or dependency problem worth addressing before the next scheduled window.

Keep the notification identity tied to this particular run so a later successful execution cannot overwrite the investigation. Review alerts that proved unhelpful and adjust the accepted population or threshold with evidence. That feedback keeps the comparison useful as normal work changes.

Maintain the Baseline as the Workload Changes

Review the comparison after deployments, schedule changes, and substantial growth. Keep failed and canceled outcomes visible in a separate reliability report. A shorter duration is not an improvement when the job quietly stops doing required work. Verify the job's result contract alongside elapsed time.

Detecting runs longer than usual is useful when the baseline has a clear population and the current activity belongs to the real Agent session. Preserve the observation, investigated cause, and operating decision. Let the alert start a focused review rather than turn a duration threshold into an automatic termination rule.

Related reading on this blog: Alerting on Long-Running Queries With a SQL Agent Job and T-SQL Script to Check SQL Server Job History.

An overrun alert is not a cancellation decision, it is evidence that the current job needs comparison with its expected work.

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