Send alert when SQL agent job runs longer than average run time history

Question

I would like to know when a specific sql agent 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

Create a query which uses the job history tables in msdb and for your job extracts the average running time. Now schedule a job which uses this query to check the currently executing job and see if it has executed longer than the average job. If it has, then use sp_send_dbmail to send en email (if that is what you want).

Answer 2

Be careful with the query of the history tables. There are circumstances where job can still show "running" even though it has errored or SQL Agent has been shutdown and restarted.

Answer 3

HI @mdhar ,

You could refer below query which could determines how long (in seconds) a specific sql agent job is currently running.

SELECT DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds  
FROM msdb..sysjobactivity aj  
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id  
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running  
AND aj.start_execution_date IS NOT NULL -- job is currently running  
AND sj.name = 'JobX'--job name  
and not exists( -- make sure this is the most recent run  
    select 1  
    from msdb..sysjobactivity new  
    where new.job_id = aj.job_id  
    and new.start_execution_date > aj.start_execution_date  
)  

You could also refer the code in below link and check whether it is helpful to you.
HOW TO: Find Currently Running Long SQL Agent Jobs

Then you could modify above code by adding sp_send_dbmail to send an email with job name and duration.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.
Hot issues November--What can I do if my transaction log is full?--- Hot issues November
Hot issues November--How to convert Profiler trace into a SQL Server table -- Hot issues November