The job that never finishes: alerting when SQL Agent jobs run much longer than normal

#The gap

A blocked ETL job, a backup writing to a slow share, an index rebuild on a table that doubled in size: none of these fail, they just keep running. The next job in the chain is skipped, and reports are late.

#The approach

1. Every 10–15 minutes, list jobs that are running now (sysjobactivity).
2. Compare elapsed time with that job's normal duration from history (median or average of recent successful runs).
3. Alert if elapsed time is more than N times the normal duration and more than an absolute minimum, so short jobs don't trigger alerts.

#The check procedure

Create in a DBA utility database:

CREATE OR ALTER PROCEDURE dbo.usp_AlertLongRunningJobs
  @Factor decimal(4,1) = 2.0,     -- alert at 2x normal
  @MinMinutes int = 30,           -- ignore anything under 30 minutes
  @Operator sysname = N'DBA Team'
AS
SET NOCOUNT ON;

WITH history AS (
  SELECT h.job_id,
         AVG(CAST((h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100 AS bigint)) AS avg_sec,
         COUNT(*) AS runs
  FROM msdb.dbo.sysjobhistory 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))
  GROUP BY h.job_id
), running AS (
  SELECT a.job_id, a.start_execution_date,
         DATEDIFF(SECOND, a.start_execution_date, GETDATE()) AS elapsed_sec
  FROM msdb.dbo.sysjobactivity a
  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
)
SELECT j.name, r.start_execution_date, r.elapsed_sec / 60 AS elapsed_min,
       h.avg_sec / 60 AS normal_min, h.runs
INTO #late
FROM running r
JOIN msdb.dbo.sysjobs j ON j.job_id = r.job_id
JOIN history h ON h.job_id = r.job_id
WHERE h.runs >= 5
  AND r.elapsed_sec >= @MinMinutes * 60
  AND r.elapsed_sec > h.avg_sec * @Factor
  AND j.name NOT IN (SELECT job_name FROM dbo.LongRunningJobExclusions);

IF EXISTS (SELECT 1 FROM #late)
BEGIN
  DECLARE @body nvarchar(max) = (
    SELECT STRING_AGG(CONCAT(name, ': running ', elapsed_min, ' min (normal ~', normal_min,
                             ' min), started ', FORMAT(start_execution_date, 'yyyy-MM-dd HH:mm')), CHAR(10))
    FROM #late);
  DECLARE @subject nvarchar(200) = CONCAT(@@SERVERNAME, ': long-running SQL Agent job(s)');
  EXEC msdb.dbo.sp_notify_operator @name = @Operator, @subject = @subject, @body = @body;
END

Supporting table:

CREATE TABLE dbo.LongRunningJobExclusions (job_name sysname PRIMARY KEY, reason nvarchar(200));

Exclude jobs that are meant to run continuously (CDC capture, replication agents, queue readers).

#Schedule it

A job DBA - Long Running Job Check running every 15 minutes, owned by sa, with its own failure notification.

To avoid repeat emails every 15 minutes for the same run, log alerted (job_name, start_execution_date) pairs in a table and skip ones already sent.

#Tuning

#When it fires

Check what the job is waiting on:

SELECT r.session_id, r.status, r.wait_type, r.wait_time, r.blocking_session_id, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE program_name LIKE 'SQLAgent - TSQL JobStep%');

Blocking, a changed execution plan or a much larger input are the usual answers.

Tip

For critical jobs, also alert when the job has not started or finished by a deadline (for example "Sales load must finish by 06:00"). That catches jobs that never started at all.