2

I have turned on notifications via email on my SQL Server Agent if any of my scheduled jobs failed or is successful.

Can anyone help me if I want to set up Alerts, if in case one of the scheduled job is running longer than expected. Best is probably thru SSMS.

4

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
begin
    exec xp_logevent 50001,'Stored procedure: some_stored_procedure has been running past threshold', informational
end

select
    ProcStart = @procStartDate, 
    CurrentDT = getdate()

Set Up Alert

General

  • 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

Response

  • 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.


Another Option

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

if exists(
SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,   
    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
WHERE
    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)

exec sp_send_dbmail
    <your parameters here> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.