locked
SQL Agent job query help RRS feed

  • General discussion

  • 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 Sunday, September 6, 2015 1:17 PM
    • Edited by Samantha v Sunday, September 6, 2015 1:20 PM typo
    Sunday, September 6, 2015 1:17 PM

All replies

  • 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/


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Sunday, September 6, 2015 8:52 PM
  • 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 .


    Madhava rao.M

    Monday, September 7, 2015 12:59 AM