Finding Abnormally Long-Running Jobs

Lori Brown has a script to find jobs running longer than their 30-day average:

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 ( 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 ( to help me avoid a divide by zero error that comes up when the average duration equals 0.

One thing which could cut down on false positives would be to calculate the standard deviation as well.  I wouldn’t automatically assume that job executions were normally distributed, but if you look at things more than one standard deviation away from the mean, it should remove noise of jobs which are just a little over the average but not in dangerous territory.

Related Posts

Quick Update Stats Job

Amy Herold has a Powershell script to generate a SQL Agent job which updates statistics for defined tables: In an effort to rule out whether or not statistics are definitely a factor, I want to UPDATE STATS on all the tables in my query, and at a specific time – sometime the day before we […]

Read More

Processing Tabular Models With Helpful Information

Ust Oldfeld has a stored procedure which runs a SQL Agent job and provides notice when processing completes: Recently, at a client, I was challenged to create a stored procedure that would process a tabular model. This stored procedure would then be executed from a web application. The process behind it being: a user enters […]

Read More


January 2017
« Dec Feb »