About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, June 4, 2012

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')

No comments:

Post a Comment