Agent job query help

Question

Agent job query help

vsamantha35

SSChampion

Points: 11551
More actions
September 6, 2015 at 7:16 am

#311405

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 limit 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

vsamantha35 (9/6/2015)
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 limit 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.

I guess my first question would be, why do you expect it to run in 5 minutes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Hi,

Its just an example. I want a query to pull jobs which are taking more than normal/deviating from baselines.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Google sysjobhistory (found in the MSDB database). It contains things like a "run_duration" column (although you need to get used to the idea of such things being store in the HHMMSS format as an INT) so that you can check completed jobs and, possibly, establish your "baseline".

https://technet.microsoft.com/en-US/library/ms174997(v=SQL.110).aspx

To determine which currently running jobs have exceed the "baseline" you create from sysjobhistory, have a look at sysjobactivity.

https://msdn.microsoft.com/en-us/library/ms190484.aspx

A decent introduction to all of this can be found in an article written by Kenneth Fisher.

http://sqlstudies.com/2013/09/05/a-t-sql-query-to-get-current-job-activity/

From there, let your imagination take over. For example, build a stored procedure that checks job activity against job history and takes a parameter for your tolerance as to what might be a job taking too long to run compared to the history. Then, ironically, write a job that runs every minute or two that runs the proc. If it returns any rows (jobs that have exceeded the tolerance), have it email you with all the information you need to see what's going on right now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11551 More actions · Answer 4

Thanks Jeff for the pointers. Will go through them.