infraJobState - State of a Job

Have you ever wondered how to interpret the State column when running the undocumented extended stored procedure xp_sqlagent_enum_jobs?  I haven't found this info in a native system table, so I created this table.  In fact I have a series of tables (I call them my infrastructure tables) which contain other snippets of info not found in the system tables (more on those later).

This proc creates table infraJobState.  You can then JOIN this with the results of  xp_sqlagent_enum_jobs  and come up with a list of jobs with current status.  See post xxx.

IF object_id('dbo.CreateInfraJobState') Is NOT Null

      DROP PROC dbo.CreateInfraJobState

go

CREATE PROC dbo.CreateInfraJobState

AS

/*    DATE        AUTHOR            REMARKS

      6/2/12            PPaiva            Initial creation.

     

      DESCRIPTION

            Creates table infraJobState.  This infrastructure table is useful

                  for mapping the State column when xp_sqlagent_enum_jobs is

                  executed.  I haven't found this info in a native system table

                  as yet.  Let me know if you do!

     

            Exec master.dbo.xp_sqlagent_enum_jobs

                        @is_sysadmin = 1,

                        @job_owner = ''

                 

      USAGE

            CreateInfraJobState

           

      DEBUG

            SELECT *

            FROM infraJobState

                       

*/

SET NOCOUNT ON 

IF object_id('dbo.infraJobState') Is Not Null

      DROP TABLE dbo.infraJobState

CREATE TABLE dbo.infraJobState(

      ID int NOT NULL CONSTRAINT pk_infraJobState PRIMARY KEY CLUSTERED,

      Description varchar(50)

      )

INSERT INTO infraJobState VALUES (0, 'Not idle or suspended')

INSERT INTO infraJobState VALUES (1, 'Executing')

INSERT INTO infraJobState VALUES (2, 'Waiting For Thread')

INSERT INTO infraJobState VALUES (3, 'Between Retries')

INSERT INTO infraJobState VALUES (4, 'Idle')

INSERT INTO infraJobState VALUES (5, 'Suspended')

INSERT INTO infraJobState VALUES (6, 'Waiting for Step to Finish')

INSERT INTO infraJobState VALUES (7, 'Performing Completion Actions')