Users should not be the first monitoring system for a runaway query. A SQL Agent job can check for long-running queries every five minutes, save each new request, and send one useful notice when it first appears. The trick is avoiding the same email on every run.
Decide What Long-Running Queries Mean Here
A five-minute threshold is an example, not a universal incident. A nightly report can be expected to run longer, while a checkout query lasting thirty seconds can already be severe. Choose a threshold by workload and give known jobs an exclusion path. I start with the requests users care about, then compare normal duration with the time needed for a DBA to respond.
SQL Server exposes currently executing requests in sys.dm_exec_requests. A request that starts and ends between job runs will not be captured. Query Store and application telemetry cover that history. This job is a live warning for work that remains active at a sample point. What action should the recipient take when the message arrives?
Find Long-Running Queries in the Live Request Set
Read total_elapsed_time in milliseconds and join to sys.dm_exec_sessions for login and application name. Exclude system sessions and known Agent job steps. Inspect the program_name values on your server before copying an exclusion pattern; applications can choose their own program names. The query below is read-only and lists long-running queries past five minutes.
SELECT r.session_id, r.request_id, r.start_time,
r.total_elapsed_time, r.status, r.command,
r.blocking_session_id, s.login_name,
s.program_name, t.text AS batch_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
AND r.total_elapsed_time >= 300000
AND s.program_name NOT LIKE N'SQLAgent - TSQL JobStep%';A request can be blocked for most of that time rather than consuming CPU. Include blocking_session_id in the alert so the first response is not to kill the wrong session. Do not email full query text without reviewing its sensitivity. Parameters and literals can include data the mail system should not keep.
Give Every Observation a Stable Identity
A session ID alone is reused. Use session_id, request_id, and request start_time as the identity for this running request. Store first_seen and last_seen, plus the observed duration and blocking session. Add a unique key so an accidental overlapping job run cannot log the same request twice. A small table in a DBA utility database is enough.
CREATE TABLE dbo.LongQueryAlertLog
(
session_id smallint NOT NULL,
request_id int NOT NULL,
request_start_time datetime NOT NULL,
first_seen datetime2(0) NOT NULL,
last_seen datetime2(0) NOT NULL,
elapsed_ms int NOT NULL,
blocking_session_id smallint NULL,
program_name nvarchar(128) NULL,
notification_sent_at datetime2(0) NULL,
CONSTRAINT PK_LongQueryAlertLog PRIMARY KEY
(session_id, request_id, request_start_time)
);Create it once in the utility database. For a rerunnable deployment, guard the CREATE and verify the existing definition. The table will hold operational metadata; restrict read access and set a retention period. Keep enough history to correlate with incidents, but do not let a simple alert log grow forever.
Insert New Requests, Then Notify
The capture procedure inserts new request identities, refreshes the last observation for existing ones, and queues mail for rows still lacking a sent timestamp. Put it in the utility database with the log table. Replace the Database Mail profile and address before scheduling it. The error path leaves notification_sent_at NULL, so a later run can retry.
CREATE OR ALTER PROCEDURE dbo.CaptureLongQueries
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now datetime2(0) = SYSDATETIME();
UPDATE l
SET last_seen = @now,
elapsed_ms = r.total_elapsed_time,
blocking_session_id = r.blocking_session_id
FROM dbo.LongQueryAlertLog AS l
JOIN sys.dm_exec_requests AS r
ON r.session_id = l.session_id
AND r.request_id = l.request_id
AND r.start_time = l.request_start_time;
INSERT dbo.LongQueryAlertLog
(session_id, request_id, request_start_time,
first_seen, last_seen, elapsed_ms,
blocking_session_id, program_name)
SELECT r.session_id, r.request_id, r.start_time,
@now, @now, r.total_elapsed_time,
r.blocking_session_id, s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
AND r.total_elapsed_time >= 300000
AND s.program_name NOT LIKE N'SQLAgent - TSQL JobStep%'
AND NOT EXISTS
(
SELECT 1 FROM dbo.LongQueryAlertLog AS l
WHERE l.session_id = r.session_id
AND l.request_id = r.request_id
AND l.request_start_time = r.start_time
);
DECLARE @sid smallint, @rid int, @started datetime, @rc int;
DECLARE @subject nvarchar(255), @body nvarchar(max);
DECLARE alerts CURSOR LOCAL FAST_FORWARD FOR
SELECT l.session_id, l.request_id, l.request_start_time
FROM dbo.LongQueryAlertLog AS l
JOIN sys.dm_exec_requests AS r
ON r.session_id = l.session_id
AND r.request_id = l.request_id
AND r.start_time = l.request_start_time
WHERE l.notification_sent_at IS NULL;
OPEN alerts;
FETCH NEXT FROM alerts INTO @sid, @rid, @started;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @subject = CONCAT(N'Long SQL request: session ', @sid);
SELECT @body = CONCAT(N'Session ', l.session_id,
N', request ', l.request_id,
N', started ', CONVERT(nvarchar(30),l.request_start_time,126),
N', elapsed milliseconds ', l.elapsed_ms,
N', blocker ', COALESCE(CONVERT(nvarchar(20),l.blocking_session_id),N'none'),
N', program ', COALESCE(l.program_name,N'unknown'))
FROM dbo.LongQueryAlertLog AS l
WHERE l.session_id = @sid AND l.request_id = @rid
AND l.request_start_time = @started;
BEGIN TRY
EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = N'ReplaceWithMailProfile',
@recipients = N'dba@example.com',
@subject = @subject, @body = @body;
IF @rc = 0
UPDATE dbo.LongQueryAlertLog
SET notification_sent_at = SYSDATETIME()
WHERE session_id = @sid AND request_id = @rid
AND request_start_time = @started;
END TRY
BEGIN CATCH
PRINT CONCAT(N'Mail failed for session ', @sid, N': ', ERROR_MESSAGE());
END CATCH;
FETCH NEXT FROM alerts INTO @sid, @rid, @started;
END;
CLOSE alerts;
DEALLOCATE alerts;
END;Give the job a single execution owner and disable overlapping runs. The unique key rejects duplicate identities if someone launches another capture concurrently. If that risk exists, add serialized application locking. The TRY block marks a row as sent only when sp_send_dbmail returns 0. A failed send prints a message instead, and the alert loop picks that active row up again on the next run.
Send One Mail for Each New Row
Configure Database Mail and a real operator address before enabling the job. The procedure loops over unsent active rows, builds a short subject, and calls msdb.dbo.sp_send_dbmail once per row. Include elapsed time, blocking session, program name, and a link to the internal incident runbook if your mail policy allows it. Do not send a new message on the next five-minute run for the same request.
Mark notification_sent_at only after sp_send_dbmail accepts the message. That is a queue acceptance, not proof of inbox delivery. If mail fails, leave the timestamp NULL and retry through a controlled notification step. I test the failure path with a disabled mail profile in a lab. A monitoring job that succeeds silently while mail is broken is not monitoring.
Schedule and Test the Agent Job
Create a SQL Agent job with a T-SQL step that runs the capture and notification procedure. Use a daily schedule repeating every five minutes. Keep the job owner and database context explicit, and add a job failure notification. Replace DBAUtility with your utility database name. Run this setup once after creating the procedure.
USE msdb;
EXEC dbo.sp_add_job
@job_name = N'DBA Long Query Alert',
@enabled = 1;
EXEC dbo.sp_add_jobstep
@job_name = N'DBA Long Query Alert',
@step_name = N'Capture and notify',
@subsystem = N'TSQL',
@database_name = N'DBAUtility',
@command = N'EXEC dbo.CaptureLongQueries;';
EXEC dbo.sp_add_schedule
@schedule_name = N'Long Query Check Every Five Minutes',
@freq_type = 4, @freq_interval = 1,
@freq_subday_type = 4, @freq_subday_interval = 5;
EXEC dbo.sp_attach_schedule
@job_name = N'DBA Long Query Alert',
@schedule_name = N'Long Query Check Every Five Minutes';
EXEC dbo.sp_add_jobserver
@job_name = N'DBA Long Query Alert';The job creation precedes the attach step. Run it manually with one controlled long request and confirm exactly one log row and one mail queue entry. Run it again while the request continues and confirm no second mail. Stop the request and check that the next test run sees no active row. Those three checks prove the deduplication path better than reading the job definition.
Keep Alerts on Long-Running Queries Useful
Review which programs are excluded and whether the threshold catches the problems users report. Keep a second route for known long maintenance work instead of suppressing every Agent session indiscriminately. A job that skips all scheduled activity can miss the very load causing the incident. Monitor the alert job's own runtime so it finishes before its next scheduled run.
I review the log with Query Store and blocking data after each alert. Sometimes the query is slow; sometimes it is waiting behind another transaction. The email should start an investigation, not prescribe a KILL command. A useful alert arrives once, carries context, and can be explained the next morning.
A mail queue entry also needs an operational owner. Check Database Mail failures, Agent job history, and the age of the last successful run. If the job has not run for an hour, an empty alert log is not evidence that queries were healthy. Keep a simple heartbeat or failed-job notification alongside the query alert.
Related reading on this blog: Long Running Queries with Execution Plan and SQL Server Alert Management: From Chaos to Clarity.
A long-query alert is not another repeated email, it is one timely clue about a new request.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
