Hi All,

Looking for help in tsql query to pull out SQL Agent job list that are executing more than normal. For instance, if job is expected to run in 5 mins threshold but it is executing for more than 1 hour, I need to pull out all such job information using sql query along with info like why it is taking more time , like blocking info, wait info so on.. How to pull out such info using a query ?

Thanks in advance.

• Changed type Samantha v 17 hours 47 minutes ago
• Edited by Samantha v 17 hours 44 minutes ago typo

http://blog.stevienova.com/2009/11/13/sql-server-agent-query-to-find-long-running-jobs/

The query mentioned in above blog helps to find the long running jobs. When you say expected to run 5 minutes, its what the user expects and cannot be determined by MSDB rather than looking into previous schedules, so the option will be to look into long running ones.

You could also look into this one.

http://thomaslarock.com/2012/10/how-to-find-currently-running-long-sql-agent-jobs/

just trying to guide towards simple resolution ..

you can get avg time for a job from msdb..sysjobs with GROUP BY job name / id put the same in another temp table and create a job to alert you / group id when the next run is above the avg time from previous run .

please check if that works .