Post reply

Checking SQL Agent Status via TSQL

  • 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

  • December 26, 2010 at 11:48 pm

    #1266360

    If you want to check the status of SQLAgent

    then check whether this query works or not

    xp_servicecontrol querystate , SQLAGENT

  • December 27, 2010 at 12:19 am

    #1266364

    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

  • December 27, 2010 at 7:45 am

    #1266442

    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

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

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