Post reply

use msdb to determine jobs that are currently running

  • March 8, 2006 at 12:09 pm

    #167480

    Hi all,

    I'm trying to get figure out if a particular job is running, but don't see an obvious status flag. Does anyone know where SQL Server maintains the current status of a job? As background, I've poked through the sysjobxxxx tables but don't see any flag that is changed when a job is actually running.

    If this info is in some other db or table please feel free to point me in the right direction!

    Thanks,

    Scott

  • March 8, 2006 at 1:35 pm

    #625520

    http://www.databasejournal.com/features/mssql/article.php/3491201

    I hope this will help you

     

    Kindest Regards,

    Amit Lohia

  • March 8, 2006 at 1:41 pm

    #625521

    Hi Scott,

    I don't think there is a table that will tell you, however, there is an SP, sp_get_composite_job_info, that does.  If you need a table, try using the extended SP, master.dbo.xp_sqlagent_enum_jobs, in conjunction with msdb.dbo.sp_verify_job_identifiers to insert values into a temp table.  Something like this:

    DECLARE

    @jobID UNIQUEIDENTIFIER,

    @userName sysname,

    @retVal INT

    -- Holding table for results from system SP execution

    CREATE TABLE #jobStatus

    (

    jobID UNIQUEIDENTIFIER NOT NULL,

    jobLastRunDate INT NOT NULL,

    jobLastRunTime INT NOT NULL,

    jobNextRunDate INT NOT NULL,

    jobNextRunTime INT NOT NULL,

    jobNextRunSched INT NOT NULL,

    jobRequestedToRun INT NOT NULL, -- BOOL

    jobRequestSource INT NOT NULL,

    jobRequestSourceID sysname COLLATE database_default NULL,

    jobRunning INT NOT NULL, -- BOOL

    jobCurrExecStep INT NOT NULL,

    jobCurrRetry INT NOT NULL,

    jobState INT NOT NULL -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions

    )

    SET @jobID = NULL

    SET @userName = SUSER_SNAME()

    -- Get job id from the name

    EXECUTE @retVal = msdb.dbo.sp_verify_job_identifiers

    '@job_name',

    '@job_id',

    @prmJobName OUTPUT,

    @jobID OUTPUT

    IF @retVal = 0

    BEGIN

    -- Populate job info

    INSERT INTO #jobStatus

    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @userName, @jobID

    END

    The basis for this code was blatantly ripped from msdb..sp_get_composite_job_info so there are no warranties written or implied (by me).

    HTH,

    Art

  • March 8, 2006 at 1:46 pm

    #625522

    Thanks to both of you!

    This is enough to get my foot in the door, and it's definitely on the right track!

    Thanks,

    Scott

  • March 8, 2006 at 1:52 pm

    #625524

    Scott,

    How about the following?  Just change your server in the OPENROWSET...

    --these are running jobs

    SELECT *

     FROM OPENROWSET('sqloledb',

        'server=ASM12;trusted_connection=yes',

        'set fmtonly off exec msdb.dbo.sp_help_job') WHERE current_execution_status <> 4

    Steve

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

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