One option would be to handle some of this logic inside your code running in the job... this works if you are doing things in batches, or if you are using a cursor or something similar.
Procedure to be Executed by Job
--create some_stored_procedure as
declare @procStartDate datetime = getdate()
declare @threshold int = 2
--doing something in batches, like batch delete, etc.
--could also uses a cursor here for reindexing, or what ever...
waitfor delay '00:00:05'
--after each batch, check the time that's expired
--if your threshold is met, put an informational message in the error log--or a fatal error if you want it to stop
if datediff(second,@procStartDate,getdate()) > @threshold
exec xp_logevent 50001,'Stored procedure: some_stored_procedure has been running past threshold', informational
ProcStart = @procStartDate,
CurrentDT = getdate()
Set Up Alert
- Name it and select Type = SQL Server event alert
- Set Severity = 10
- Check "Raise Alert when message contains" and enter "some_stored_procedure" or something unique to the message in xp_logevent
- Check Notify operator and set the operator
That is, test it out! You can just create the alert and run the code above without creating a proc and it should email you.
If this ins't feasible, and your threshold is high enough where you wouldn't mind running a job every X minutes, you could create a job to query the
sys.jobactivity for jobs running that don't have a stop time and then send an email with
sp_send_dbmail. Or, if your job has a lot of small steps, add this to the end of each step. What ever works for your environment.
declare @minuteThreshold int = 20
j.name AS job_name,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
start_execution_date is not null
AND stop_execution_date is null
AND j.name = 'yourJobName'
AND datediff(minute,ja.start_execution_date,getdate()) > @minuteThreshold)
<your parameters here>