SQL Server DBA: Monitor Long-Running Agent Jobs and Send Email Alerts Using T-SQL
A practical SQL Server DBA monitoring solution to track SQL Server Agent jobs, compare execution time with expected duration, maintain historical counters, and send HTML email alerts.
In day-to-day SQL Server DBA operations, one common challenge is SQL Server Agent jobs that continue running for hours without failing. These jobs may consume CPU, memory, disk I/O, delay dependent jobs, and affect scheduled maintenance activities. DBAs may discover the issue only after another job is delayed or an application team reports a performance problem.
This article presents a T-SQL monitoring approach that checks active job executions, maintains duration statistics, and sends an HTML email alert when a monitored job exceeds its configured expected duration.
What this solution does
- Maintains a configurable list of SQL Server Agent jobs.
- Stores expected duration and an exclusion flag for each job.
- Identifies active jobs using
msdb.dbo.sysjobactivity. - Tracks completed job durations in historical buckets.
- Sends an HTML email report for jobs that exceed their configured threshold.
Prerequisites
- The
DBAdatabase must exist. - SQL Server Agent must be running.
- Database Mail must be configured and tested.
- The monitoring job's execution context must have the required permissions to read job metadata and send email.
Step 1: One-Time Setup Script
Run this script once. It creates the monitoring table and registers the existing SQL Server Agent jobs. The initial expected duration is derived from available job history, with a 180-minute fallback.
USE DBA;
GO
IF OBJECT_ID('dbo.SqlAgentJobs', 'U') IS NULL
BEGIN
CREATE TABLE dbo.SqlAgentJobs
(
ID INT IDENTITY(1,1) PRIMARY KEY,
JobName SYSNAME NOT NULL UNIQUE,
Instance_Id INT NULL,
[Expected-Max-Duration(Min)] INT NOT NULL
CONSTRAINT DF_SqlAgentJobs_ExpectedDuration DEFAULT (180),
[Ignore] BIT NOT NULL
CONSTRAINT DF_SqlAgentJobs_Ignore DEFAULT (0),
[Running Since] DATETIME2(0) NULL,
[<3-Hrs] BIGINT NOT NULL DEFAULT (0),
[3-Hrs] BIGINT NOT NULL DEFAULT (0),
[6-Hrs] BIGINT NOT NULL DEFAULT (0),
[9-Hrs] BIGINT NOT NULL DEFAULT (0),
[12-Hrs] BIGINT NOT NULL DEFAULT (0),
[18-Hrs] BIGINT NOT NULL DEFAULT (0),
[24-Hrs] BIGINT NOT NULL DEFAULT (0),
[36-Hrs] BIGINT NOT NULL DEFAULT (0),
[48-Hrs] BIGINT NOT NULL DEFAULT (0),
CollectionTime DATETIME2(0) NOT NULL DEFAULT (SYSDATETIME())
);
END;
GO
;WITH JobDuration AS
(
SELECT
j.job_id,
j.name AS JobName,
AVG(
(
(h.run_duration / 10000) * 3600
+ ((h.run_duration / 100) % 100) * 60
+ (h.run_duration % 100)
) / 60.0
) AS AverageDurationMinutes
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobhistory AS h
ON h.job_id = j.job_id
AND h.step_id = 0
GROUP BY j.job_id, j.name
)
INSERT INTO DBA.dbo.SqlAgentJobs
(
JobName, Instance_Id, [Expected-Max-Duration(Min)], [Ignore]
)
SELECT
d.JobName,
(
SELECT MAX(h.instance_id)
FROM msdb.dbo.sysjobhistory AS h
WHERE h.job_id = d.job_id
AND h.step_id = 0
),
CASE
WHEN d.AverageDurationMinutes IS NULL THEN 180
WHEN d.AverageDurationMinutes < 1 THEN 180
WHEN d.AverageDurationMinutes > 2147483647 THEN 2147483647
ELSE CONVERT(INT, CEILING(d.AverageDurationMinutes))
END,
CASE
WHEN EXISTS
(
SELECT 1
FROM msdb.dbo.syscategories AS c
INNER JOIN msdb.dbo.sysjobs AS j
ON j.category_id = c.category_id
WHERE j.job_id = d.job_id
AND c.name LIKE 'repl%'
)
OR d.JobName LIKE 'DBA%'
THEN 1 ELSE 0
END
FROM JobDuration AS d
WHERE NOT EXISTS
(
SELECT 1
FROM DBA.dbo.SqlAgentJobs AS b
WHERE b.JobName = d.JobName
);
GO
-- Optional: override expected duration for all monitored jobs.
-- UPDATE DBA.dbo.SqlAgentJobs
-- SET [Expected-Max-Duration(Min)] = 180
-- WHERE [Ignore] = 0;
SELECT *
FROM DBA.dbo.SqlAgentJobs
ORDER BY JobName;
GOStep 2: Recurring Monitoring and Email Alert Script
Create a SQL Server Agent job named DBA - Long Running Jobs and use the following script as its T-SQL job step. Update the recipient and Database Mail profile before enabling the schedule.
USE DBA;
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @AlertThresholdMinutes INT = 180;
DECLARE @Recipients VARCHAR(MAX) = 'ajay@sqldbanow.com';
DECLARE @MailProfile SYSNAME = 'DBA';
DECLARE @Subject NVARCHAR(255);
DECLARE @Body NVARCHAR(MAX);
DECLARE @Rows NVARCHAR(MAX);
DECLARE @ServerName SYSNAME = CONVERT(SYSNAME, @@SERVERNAME);
------------------------------------------------------------
-- STEP 1: Collect new completed job history
------------------------------------------------------------
IF OBJECT_ID('tempdb..#NewHistory') IS NOT NULL DROP TABLE #NewHistory;
;WITH NewHistory AS
(
SELECT
j.name AS JobName,
h.instance_id,
TotalMinutes =
(h.run_duration / 10000) * 60
+ ((h.run_duration / 100) % 100)
+ CASE WHEN h.run_duration % 100 >= 30 THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = j.job_id
INNER JOIN DBA.dbo.SqlAgentJobs AS b ON b.JobName = j.name
WHERE h.step_id = 0
AND b.[Ignore] = 0
AND h.instance_id > ISNULL(b.Instance_Id, 0)
)
SELECT
JobName,
instance_id,
TotalMinutes,
TimeRange =
CASE
WHEN TotalMinutes >= 2880 THEN '48-Hrs'
WHEN TotalMinutes >= 2160 THEN '36-Hrs'
WHEN TotalMinutes >= 1440 THEN '24-Hrs'
WHEN TotalMinutes >= 1080 THEN '18-Hrs'
WHEN TotalMinutes >= 720 THEN '12-Hrs'
WHEN TotalMinutes >= 540 THEN '9-Hrs'
WHEN TotalMinutes >= 360 THEN '6-Hrs'
WHEN TotalMinutes >= 180 THEN '3-Hrs'
ELSE '<3-Hrs'
END
INTO #NewHistory
FROM NewHistory;
------------------------------------------------------------
-- STEP 2: Aggregate new history rows per job
------------------------------------------------------------
IF OBJECT_ID('tempdb..#HistoryCounts') IS NOT NULL DROP TABLE #HistoryCounts;
SELECT
JobName,
MAX(instance_id) AS MaxInstanceId,
SUM(CASE WHEN TimeRange = '<3-Hrs' THEN 1 ELSE 0 END) AS C_LT3,
SUM(CASE WHEN TimeRange = '3-Hrs' THEN 1 ELSE 0 END) AS C_3,
SUM(CASE WHEN TimeRange = '6-Hrs' THEN 1 ELSE 0 END) AS C_6,
SUM(CASE WHEN TimeRange = '9-Hrs' THEN 1 ELSE 0 END) AS C_9,
SUM(CASE WHEN TimeRange = '12-Hrs' THEN 1 ELSE 0 END) AS C_12,
SUM(CASE WHEN TimeRange = '18-Hrs' THEN 1 ELSE 0 END) AS C_18,
SUM(CASE WHEN TimeRange = '24-Hrs' THEN 1 ELSE 0 END) AS C_24,
SUM(CASE WHEN TimeRange = '36-Hrs' THEN 1 ELSE 0 END) AS C_36,
SUM(CASE WHEN TimeRange = '48-Hrs' THEN 1 ELSE 0 END) AS C_48
INTO #HistoryCounts
FROM #NewHistory
GROUP BY JobName;
------------------------------------------------------------
-- STEP 3: Update history counters
------------------------------------------------------------
UPDATE b
SET
b.Instance_Id = h.MaxInstanceId,
b.[<3-Hrs] = b.[<3-Hrs] + h.C_LT3,
b.[3-Hrs] = b.[3-Hrs] + h.C_3,
b.[6-Hrs] = b.[6-Hrs] + h.C_6,
b.[9-Hrs] = b.[9-Hrs] + h.C_9,
b.[12-Hrs] = b.[12-Hrs] + h.C_12,
b.[18-Hrs] = b.[18-Hrs] + h.C_18,
b.[24-Hrs] = b.[24-Hrs] + h.C_24,
b.[36-Hrs] = b.[36-Hrs] + h.C_36,
b.[48-Hrs] = b.[48-Hrs] + h.C_48,
b.CollectionTime = SYSDATETIME()
FROM DBA.dbo.SqlAgentJobs AS b
INNER JOIN #HistoryCounts AS h ON h.JobName = b.JobName;
------------------------------------------------------------
-- STEP 4: Find currently running jobs
------------------------------------------------------------
IF OBJECT_ID('tempdb..#RunningJobs') IS NOT NULL DROP TABLE #RunningJobs;
;WITH LatestAgentSession AS
(
SELECT TOP (1) session_id
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC
)
SELECT
j.name AS JobName,
ja.start_execution_date AS RunningSince,
DATEDIFF(MINUTE, ja.start_execution_date, SYSDATETIME()) AS RunningMinutes
INTO #RunningJobs
FROM msdb.dbo.sysjobactivity AS ja
INNER JOIN LatestAgentSession AS s ON s.session_id = ja.session_id
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = ja.job_id
INNER JOIN DBA.dbo.SqlAgentJobs AS b ON b.JobName = j.name
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
AND b.[Ignore] = 0;
------------------------------------------------------------
-- STEP 5: Refresh current running status
------------------------------------------------------------
UPDATE DBA.dbo.SqlAgentJobs
SET [Running Since] = NULL;
UPDATE b
SET
b.[Running Since] = r.RunningSince,
b.CollectionTime = SYSDATETIME()
FROM DBA.dbo.SqlAgentJobs AS b
INNER JOIN #RunningJobs AS r ON r.JobName = b.JobName;
------------------------------------------------------------
-- STEP 6: Build HTML table rows
------------------------------------------------------------
SET @Subject = N'Long Running SQL Agent Jobs - ' + @ServerName;
SET @Rows = CAST
(
(
SELECT
td = b.JobName, '',
td = b.[Expected-Max-Duration(Min)], '',
td = CONVERT(VARCHAR(19), r.RunningSince, 120), '',
td = CAST(r.RunningMinutes / 60.0 AS DECIMAL(10,2)), '',
td = ISNULL(b.[<3-Hrs], 0), '',
td = ISNULL(b.[3-Hrs], 0), '',
td = ISNULL(b.[6-Hrs], 0), '',
td = ISNULL(b.[9-Hrs], 0), '',
td = ISNULL(b.[12-Hrs], 0), '',
td = ISNULL(b.[18-Hrs], 0), '',
td = ISNULL(b.[24-Hrs], 0), '',
td = ISNULL(b.[36-Hrs], 0), '',
td = ISNULL(b.[48-Hrs], 0)
FROM DBA.dbo.SqlAgentJobs AS b
INNER JOIN #RunningJobs AS r ON r.JobName = b.JobName
WHERE r.RunningMinutes >=
CASE WHEN b.[Expected-Max-Duration(Min)] > 0
THEN b.[Expected-Max-Duration(Min)]
ELSE @AlertThresholdMinutes END
ORDER BY r.RunningMinutes DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
);
------------------------------------------------------------
-- STEP 7: Send email only when qualifying jobs exist
------------------------------------------------------------
IF EXISTS
(
SELECT 1
FROM DBA.dbo.SqlAgentJobs AS b
INNER JOIN #RunningJobs AS r ON r.JobName = b.JobName
WHERE r.RunningMinutes >=
CASE WHEN b.[Expected-Max-Duration(Min)] > 0
THEN b.[Expected-Max-Duration(Min)]
ELSE @AlertThresholdMinutes END
)
BEGIN
SET @Body =
N'<html><head><style>
body {font-family:Arial,sans-serif;}
table {border-collapse:collapse;width:100%;}
th,td {border:1px solid #ddd;padding:8px;text-align:left;}
th {background-color:#267343;color:white;}
tr:nth-child(even) {background-color:#f2f2f2;}
</style></head><body>' +
N'<h2>Long Running SQL Agent Jobs</h2>' +
N'<p>Server: ' + @ServerName + N'</p>' +
N'<p>Jobs exceeding their configured duration threshold:</p>' +
N'<table><thead><tr>' +
N'<th>Job Name</th><th>Expected Minutes</th><th>Running Since</th>' +
N'<th>Running Hours</th><th><3 Hrs</th><th>>=3 Hrs</th>' +
N'<th>>=6 Hrs</th><th>>=9 Hrs</th><th>>=12 Hrs</th>' +
N'<th>>=18 Hrs</th><th>>=24 Hrs</th><th>>=36 Hrs</th>' +
N'<th>>=48 Hrs</th></tr></thead><tbody>' +
ISNULL(@Rows, N'') +
N'</tbody></table><br/><p>Regards,<br/>SQL DBA Monitoring</p>' +
N'<p>Alert generated by SQL Agent job: DBA - Long Running Jobs</p>' +
N'</body></html>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients = @Recipients,
@subject = @Subject,
@body = @Body,
@body_format = 'HTML';
END
ELSE
BEGIN
PRINT 'No jobs exceeded their configured duration threshold.';
END;
------------------------------------------------------------
-- STEP 8: Display currently running monitored jobs
------------------------------------------------------------
SELECT
b.JobName,
b.[Expected-Max-Duration(Min)] AS ExpectedMinutes,
r.RunningSince,
r.RunningMinutes,
CAST(r.RunningMinutes / 60.0 AS DECIMAL(10,2)) AS RunningHours,
b.[<3-Hrs], b.[3-Hrs], b.[6-Hrs], b.[9-Hrs], b.[12-Hrs],
b.[18-Hrs], b.[24-Hrs], b.[36-Hrs], b.[48-Hrs]
FROM DBA.dbo.SqlAgentJobs AS b
INNER JOIN #RunningJobs AS r ON r.JobName = b.JobName
WHERE b.[Ignore] = 0
ORDER BY r.RunningMinutes DESC;
GOStep 3: Configure the SQL Server Agent Schedule
- Open SQL Server Management Studio (SSMS).
- Expand SQL Server Agent → Jobs and create
DBA - Long Running Jobs. - Add the monitoring script as a T-SQL job step.
- Schedule it to run every 5 or 10 minutes, depending on your operational needs.
- Confirm the Database Mail profile name matches
@MailProfileand replace the sample recipient address. - Run the job manually, then verify job history and email delivery.
Sample Email Report
When a job exceeds its configured duration threshold, the monitoring script sends an HTML email with the server name, job name, expected duration, start time, elapsed hours, and historical counters.
Illustrative sample report image. Job names, timestamps, and counter values are examples only; they are not live SQL Server results.
Example report data
| Job Name | Expected Minutes | Running Hours | Example status |
|---|---|---|---|
| ETL_Load_Data | 60 | 4.25 | Exceeds expected duration |
| Index_Maintenance | 120 | 6.50 | Exceeds expected duration |
| Report_Generation | 180 | 12.75 | Exceeds expected duration |
These rows are illustrative examples only.
Important Production Considerations
- Expected duration: Historical average duration is a starting point, not necessarily a formal SLA. Review each job's threshold.
- Repeated alerts: This script sends an alert each time it runs while a job remains above its threshold. Add a cooldown or alert log if repeated messages are undesirable.
- Historical counters: Counters are cumulative. Monitor SQL Agent history retention and validate that each history record is processed only once.
- Job changes: Review the configuration if jobs are renamed, deleted, or recreated.
- Permissions and mail: Test Database Mail, SQL Agent execution permissions, and email delivery before production deployment.
- Testing: Validate on a non-production instance before scheduling on critical servers.
Technical note: SQL Agent stores
run_duration in an HHMMSS-style integer, not as a decimal number of minutes. The script converts it into minutes for the historical buckets. For mission-critical monitoring, consider adding transaction protection, overlap prevention, a persistent alert log, and alert deduplication.Conclusion
Proactive SQL Server Agent job monitoring helps DBAs identify unexpected execution times before they affect dependent jobs or application workloads. By combining active execution checks, historical duration counters, configurable thresholds, and Database Mail notifications, this solution provides a useful starting point for improving SQL Server operational visibility.
