Find Currently Executing Jobs Running Longer Than 30 Day Average

By Lori Brown | Helpful Scripts

Jan 19

–By Lori Brown   @SQLSupahStah

I needed to update some of our long running job monitoring code to improve it from the version that we have right now. I like this version because it uses msdb.dbo.syssessions (https://msdn.microsoft.com/en-us/library/ms175016.aspx) to validate that a job is actually running. I also wanted to know the percent difference between the current run duration versus an average duration per job from the past 30 days. I decided to place the calculated average into a table variable and then join on it to get my results. I also used the IIF function (https://msdn.microsoft.com/en-us/library/hh213574.aspx) to help me avoid a divide by zero error that comes up when the average duration equals 0.

I also learned about a function that I had never heard of before, msdb.dbo.agent_datetime. Interestingly, I cannot find documentation of this function from MSDN. However, this had apparently been around for a while. I don’t feel too bad about not knowing about this one since even super smart Pinal Dave had only recently found it too (https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/)

— currently executing jobs running longer than 30 day avg runtime

declare @startdt date

declare @enddt date

declare @JobsAvgDuration table (job_id uniqueidentifier, JobName varchar(250), ThirtyDayAvgDurMin int)

 

set @startdt = getdate()-30

set @enddt = getdate()

 

insert into @JobsAvgDuration

select j.job_id, j.name as JobName,

AVG((h.run_duration/10000*3600 + (h.run_duration/100)%100*60 + h.run_duration%100 + 31 ) / 60) as ThirtyDayAvgDurMin

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobhistory h on (j.job_id = h.job_id)

where j.enabled = 1

and msdb.dbo.agent_datetime(h.run_date, 0) between @startdt and @enddt

group by j.job_id, j.name

order by j.name

 

select j.name as LongRunningJobName,

datediff(mi, a.start_execution_date, getdate()) as MinsRunning,

d.ThirtyDayAvgDurMin,

(datediff(mi, a.start_execution_date, getdate())-d.ThirtyDayAvgDurMin)*100/IIF(d.ThirtyDayAvgDurMin=0,1,d.ThirtyDayAvgDurMin) as PercentDiff

from msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity a on (j.job_id = a.job_id)

join @JobsAvgDuration d on (d.job_id = j.job_id)

where a.session_id in (select session_id from msdb.dbo.syssessions) — active session

and (a.start_execution_date IS NOT NULL and a.stop_execution_date IS NULL)

and datediff(mi, a.start_execution_date, getdate()) > d.ThirtyDayAvgDurMin

I had to force one of my monitoring jobs to run long so I could show you the results.

blog_20170119_1

All that is left is to put the results into a table or a report and I will be done. Let me know if you have a unique way of monitoring for jobs that run extraordinarily long. We would love to see other solutions!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>