SQL SERVER: Get Long Running Job History

sp_GetLongRunningJobHistory.sql

USE [master]
GO

CREATE OR ALTER PROC dbo.sp_GetLongRunningJobHistory (
	@DaysPast INT = 1, 
	@HistoryStartDate DATETIME = NULL,		/* @HistoryStartDate - Start date for historical average */
	@HistoryEndDate DATETIME = NULL, 		/* @HistoryEndDate - End date for historical average */
	@MinHistExecutions INT = 1.0, 			/* @MinHistExecutions - Minimum number of job runs we want to consider */
	@MinAvgSecsDuration INT = 1.0  			/* @MinAvgSecsDuration - Threshold for minimum duration we care to monitor */
) AS 
BEGIN
	SET @DaysPast = -ABS(@DaysPast)
	/*=============================================
		File: long_running_jobs.sql

		Author: Thomas LaRock, https://thomaslarock.com/contact-me/
		Source: https://thomaslarock.com/2012/10/how-to-find-currently-running-long-sql-agent-jobs/
		Summary: This script will check to see if any currently
							running jobs are running long. 
		Modifications: 
			Tim Cartwright
				- modified to also output the duration secs
				- changed to output any job, running or not
				- altered to only report jobs that fell outside of the deviation in the last N (parameter) days

		Parameters:
			@MinHistExecutions - Minimum number of job runs we want to consider 
			@MinAvgSecsDuration - Threshold for minimum duration we care to monitor
			@HistoryStartDate - Start date for historical average
			@HistoryEndDate - End date for historical average

			These variables allow for us to control a couple of factors. First
			we can focus on jobs that are running long enough on average for
			us to be concerned with (say, 30 seconds or more). Second, we can
			avoid being alerted by jobs that have run so few times that the
			average and standard deviations are not quite stable yet. This script
			leaves these variables at 1.0, but I would advise you alter them
			upwards after testing.

		Returns: One result set containing a list of jobs that
			are currently running and are running longer than two standard deviations 
			away from their historical average. The "Min Threshold" column
			represents the average plus two standard deviations. 

		Date: October 3rd, 2012

		SQL Server Versions: SQL2005, SQL2008, SQL2008R2, SQL2012

		You may alter this code for your own purposes. You may republish
		altered code as long as you give due credit. 

		THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
		OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
		LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
		FITNESS FOR A PARTICULAR PURPOSE.

	=============================================*/

	SELECT @HistoryStartDate = ISNULL(@HistoryStartDate, DATEADD(MONTH, -12, GETDATE()))
		, @HistoryEndDate = ISNULL(@HistoryEndDate, GETDATE())

	;WITH JobHistData AS (
		SELECT job_id
			,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
			,secs_duration= (run_duration/10000*3600) + ((run_duration/100)%100*60) + (run_duration%100)
		FROM msdb.dbo.sysjobhistory
		WHERE step_id = 0   --Job Outcome
		AND run_status = 1  --Succeeded
	), JobHistStats AS (
		SELECT job_id
			,AvgDuration = AVG(secs_duration*1.)
			,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
		FROM JobHistData
		WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
			AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
		GROUP BY job_id   
		HAVING COUNT(*) >= @MinHistExecutions
		AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
	)

	SELECT @@SERVERNAME AS [server_name], 
		[j].[name] AS [job_name],
		[jd].[date_executed] AS [execution_date],
		CAST([jd].[secs_duration] AS DECIMAL(19,2)) AS [duration_secs],
		CAST([jhs].[AvgDuration] AS DECIMAL(19,2)) AS [avg_duration_secs],
		CAST([jhs].[AvgPlus2StDev] AS DECIMAL(19,2)) AS [avg_plus2_stdev_secs],
		CAST([jd].[secs_duration] - [jhs].[AvgPlus2StDev] AS DECIMAL(19,2)) AS [differential]
	FROM [JobHistData] AS [jd]
	JOIN [JobHistStats] AS [jhs]
		ON [jd].[job_id] = [jhs].[job_id]
	JOIN [msdb].[dbo].[sysjobs] AS [j]
		ON [jd].[job_id] = [j].[job_id]
	WHERE [jd].[secs_duration] > [jhs].[AvgPlus2StDev]
		AND [jd].[date_executed] >= CAST(DATEADD(DAY, @DaysPast, GETDATE()) AS DATE)
		AND ([jd].[secs_duration] - [jhs].[AvgPlus2StDev]) > 1.0
	GROUP BY [jd].[job_id],
		[j].[name],
		[jd].[date_executed],
		[jd].[secs_duration],
		[jhs].[AvgDuration],
		[jhs].[AvgPlus2StDev]
	ORDER BY [jd].[date_executed]
END		 
GO

EXECUTE sys.[sp_MS_marksystemobject] @objname = N'dbo.sp_GetLongRunningJobHistory'
GO                                     

GRANT EXECUTE ON dbo.sp_GetLongRunningJobHistory TO PUBLIC
GO