Send alert when job runs longer than average run time

Question

I would like to know when a specific job is running longer than average run time history and then send a email alert with job name and duration. Any help is appreciated. Thanks.

Answer 1

Hi @Ozra Hasannejad ,

Welcome to Microsoft Q&A!
I found a good blog you can refer to. Perhaps, you will get some inspiration from it.
IDENTIFYING LONG RUNNING SQL AGENT JOBS SCRIPT
The example is to alert the DBA team using database mail if the currently running job exceeds 150% of the average (any number you can change).

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You could have the job to start a second job with sp_start_job. This second job would read the history for the first job, compute the expected execution time, and then do a WAITFOR. When then WAITFOR elapsed, it checks if the first job is still executing and in such case the job would send the alert.

A variation is to use Service Broker and a Conversation Timer. But you would only do this, if you are already using Service Broker.

Answer 3

If you know about dbachecks you can create a powershell job to monitor long runningjobs,

invoke-dbcchecks -computername xyz -checks longrunningjobs--,

you can find the full syntax from https://dbatools.io/introducing-dbachecks/