Checking SQL Agent Status via TSQL

Question

Post reply

Checking SQL Agent Status via TSQL

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
December 24, 2010 at 11:34 am

#231018

I can easily find the current execution status of my job, but having difficulty determing which step it's currently executing at:
MYLINKEDSERVER.msdb.dbo.sp_help_job '4458FBFB-62D6-4842-B595-6A0DEA77FD68'
And this gives me back the status telling me it's still executing and that there are 9 steps in total, but I'd like to determine which step out of 9 it's currently executing
Example:
DECLARE @Idx int SET @Idx = 1
DECLARE @JobComplete int, @JobStep int
WHILE @Idx <= 3
BEGIN
/* Check to see if the job has completed */
SET @JobComplete = (
SELECT current_execution_status
FROM OPENQUERY(LINKEDSERVER, 'set fmtonly off; exec msdb.dbo.sp_help_job')
WHERE job_id = N'4458FBFB-62D6-4842-B595-6A0DEA77FD68'
)
SET @JobComplete = ???
IF (@JobComplete = 4 AND @JobStep = 7)
-- Do something here
SET @Idx = @Idx + 1
/* Wait for a while then try again */
WAITFOR DELAY '0:05:00'
END
Is there a system sp or a way to link this together so I can get the currently executing step of the job?
Thanks in advance
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

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

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

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 1

If you want to check the status of SQLAgent

then check whether this query works or not

xp_servicecontrol querystate , SQLAGENT

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 2

The result set of sp_help_job has a column - "current_execution_step", which provides the "Current execution step in the job."

This is mentioned in Books On Line, and hence would request you to please refer the same for further details: http://msdn.microsoft.com/en-us/library/ms186722.aspx

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Thanks for your replies, I think I had found it a few days ago but if there is a more preferred/accurate method, I'd rather use it than what I have:

SET @JobStep = (

SELECT last_executed_step_id

FROM MYLINKEDSERVER.msdb.dbo.sysjobactivity

WHERE job_id = '4458FBFB-62D6-4842-B595-6A0DEA77FD68'

AND start_execution_date >= CONVERT(varchar(10), GETDATE(),101)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience