Send alert when job runs longer than average run time

Ozra Hasannejad 21 Reputation points
2022-10-04T11:58:46.113+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,451 Reputation points
    2022-10-05T02:25:31.237+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2022-10-04T21:37:31.413+00:00

    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.


  2. wondwossen woldetensay 1 Reputation point
    2022-10-04T21:58:18.22+00:00

    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/