Jobs Running Longer than Average Run Time

create procedure dbo.usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds int,

@EmailRecipients varchar(128) = 'email@domain.com'

as

/*

Created By: Dulari Parikh

Purpose: Executed to stop jobs stuck in a hanged state. Can be used to simply report jobs running longer than average run time

usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds = 3600

usp_Dba_JobsExceedingAvgRunTime

@ExceededBySeconds = 1

*/

declare @JobId uniqueidentifier

declare @JobName varchar(128)

if exists

(

select

ed.job_id AS 'JobId',

ed.name AS 'JobName',

ed.start_execution_date AS 'StartTime',

ja.stop_execution_date AS 'StopTime',

art.AvgRuntimeOnSucceed,

DATEDIFF(ss,ja.start_execution_date,GETDATE()) as CurrentRunTime

from

(

select j.name, j.job_id, MAX(start_execution_date) as start_execution_date

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id

group by j.name, j.job_id

) ed

join msdb.dbo.sysjobactivity ja on ed.start_execution_date = ja.start_execution_date and ed.job_id = ja.job_id

join

(

SELECT job_id,

AVG

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)

+

STDEV

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS AvgRuntimeOnSucceed

FROM msdb.dbo.sysjobhistory

WHERE step_id = 0 AND run_status = 1

GROUP BY job_id

) art ON ed.job_id = art.job_id

WHERE

ja.stop_execution_date IS NULL

and DATEDIFF(ss,ja.start_execution_date,GETDATE()) > art.AvgRuntimeOnSucceed + @ExceededBySeconds -- Runs longer than @ExceededBySeconds over the average successful runtime

)

BEGIN

delete from JobsLongRunning

insert into JobsLongRunning

(

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

)

select

ed.job_id AS 'JobId',

ed.name AS 'JobName',

ed.start_execution_date AS 'JobStartTime',

art.AvgRuntimeOnSucceed,

DATEDIFF(ss,ja.start_execution_date,GETDATE()) as CurrentRunTime

from

(

select j.name, j.job_id, MAX(start_execution_date) as start_execution_date

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id

group by j.name, j.job_id

) ed

join msdb.dbo.sysjobactivity ja on ed.start_execution_date = ja.start_execution_date and ed.job_id = ja.job_id

join

(

SELECT job_id,

AVG

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)

+

STDEV

((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS AvgRuntimeOnSucceed

FROM msdb.dbo.sysjobhistory

WHERE step_id = 0 AND run_status = 1

GROUP BY job_id

) art ON ed.job_id = art.job_id

WHERE

ja.stop_execution_date IS NULL

and DATEDIFF(ss,ja.start_execution_date,GETDATE()) > art.AvgRuntimeOnSucceed + @ExceededBySeconds -- @ExceededBySeconds -- Runs longer than @ExceededBySeconds over the average successful runtime

--ORDER BY ja.start_execution_date DESC

select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning

exec msdb..sp_send_dbmail

@recipients = @EmailRecipients,

@subject = 'ETL SQL Job Exceeded Average Run Time',

@query = 'select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning'

create table #Jobs

(

JobId uniqueidentifier,

JobName varchar(128),

JobStartTime datetime,

AvgRuntimeOnSucceed int,

CurrentRunTime int

)

insert into #Jobs

(

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

)

select

JobId,

JobName,

JobStartTime,

AvgRuntimeOnSucceed,

CurrentRunTime

from DBAdmin.dbo.JobsLongRunning

while exists(select * from #Jobs)

begin

select top 1 @JobName = JobName from #Jobs

exec msdb..sp_stop_job @job_name = @JobName

delete from #Jobs where JobName = @JobName

end

END

GO