–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,
(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.
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!