Check on Long Running SQL Agent Job

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:

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 &gt; @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

bzzzt!

What's Going On?

Check on Long Running SQL Agent Job