Although SQL Agent Jobs can be configured to alert users when a job has passed on failed, there is no mechanism to alert users if the job has been running a long time. So I wrote a stored procedure that can be executed using a SQL Agent Job (ironically.) I’ve written it so that you can check individual jobs as opposed to all jobs on a server, as undoubtedly there are some job that I’m not too bothered about if they run longer than others (e.g. log backup durations generally alter depending on the load of the server.)
The job is also configured to send emails. I’m not a big fan of sending emails out as part of jobs, because we all know where these emails end up, which is why I wrote the sproc to run at the job level, but also include a percentage threshold: when a job duration deviates from its baseline duration by more than a percentage threshold will only then an email be sent.I’ve included comments in the sproc, but what it does is:
- Check if job is running (if it isn’t the sproc stops right there.)
- If job is running, get average runtime of job
- Compare the two, and if the difference in terms of percentage is greater than the threshold then an email is sent.
USE [master] GO /****** Object:StoredProcedure [dbo].[AlertOnLongRunningJobs]Script Date: 08/10/2015 15:57:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --job name you want to monitor --percentage difference threshold --db mail settings CREATE PROCEDURE [dbo].[AlertOnLongRunningJobs] ( @jobName NVARCHAR(100) ,@pct DECIMAL(10, 2) ,@db_mail_profile NVARCHAR(100) ,@db_mail_recipients NVARCHAR(100) ) AS SET NOCOUNT ON --the percentage increase between the average run time and the runtime of the job running now DECLARE @increase DECIMAL(10, 2) DECLARE @runtime DECIMAL(10, 2) DECLARE @avgRunTime DECIMAL(10, 2) --db mail settings DECLARE @db_mail_body NVARCHAR(512) DECLARE @db_mail_subject NVARCHAR(256) CREATE TABLE #runningJobs ( running_job_id UNIQUEIDENTIFIER ,running_job_name NVARCHAR(100) ,runtime VARCHAR(8) ) CREATE TABLE #jobHistory ( job_id UNIQUEIDENTIFIER ,job_name NVARCHAR(100) ,avgRunTime VARCHAR(8) ) --check if job is running now INSERT INTO #runningJobs SELECT j.job_id ,j.NAME AS job_name ,CONVERT(VARCHAR, (GETDATE() - ja.start_execution_date), 108) FROM msdb.dbo.sysjobactivity ja INNER 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 = @jobName --if the job is running now, then we execute rest of query, or we just stop here. IF EXISTS ( SELECT 1 FROM #runningJobs ) INSERT INTO #jobHistory SELECT jobid = job_id ,job_name = NAME ,avg_hhmmss = CONVERT(CHAR(8), DATEADD(second, rd, 0), 108) FROM ( SELECT j.NAME ,j.job_id ,COUNT(*) AS NumberOfRuns ,rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 0, ':'), 3, 0, ':'))) FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id WHERE h.step_id = 0 AND j.NAME = @jobName GROUP BY j.NAME ,j.job_id ) AS rs ORDER BY job_name --get run time of current job and average runtime SELECT @runtime = DATEDIFF(SECOND, 0, runtime) FROM #runningJobs SELECT @avgRunTime = DATEDIFF(SECOND, 0, avgRunTime) FROM #jobHistory --set the percentage increase of the current run of job. --if it is greater than percentage threshold then return job name. SET @increase = @runtime - @avgRunTime SET @increase = (@increase / @avgRunTime) * 100 IF (@increase > @pct) BEGIN SET @db_mail_subject = 'Long Running SQL Agent Job on ' + ( SELECT @@SERVERNAME ) SET @db_mail_body = 'The job ' + @jobName + ' has been running for ' + ( SELECT runtime FROM #runningJobs ) + ' (hhmmss). The avergage duration for this job is ' + ( SELECT avgRunTime FROM #jobHistory ) + '(hhmmss). This is a ' + CAST(@increase AS NVARCHAR(12)) + '% increase. Please investigate.' --EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_mail_profile -- ,@recipients = @db_mail_recipients -- ,@body = @db_mail_body -- ,@subject = @db_mail_subject; SELECT @db_mail_body DROP TABLE #runningJobs DROP TABLE #jobHistory END GO