JobStatus

This proc JOINs the results of xp_sqlagent_enum_jobs with infraJobState to produce a list of jobs with the current status.  


Very useful on a busy server with lots of jobs kicking off at different times.

This proc is dependent upon these objects:
        infraJobState
        vwJob

Use Admin

GO

IF Object_ID('dbo.JobStatus') Is Not Null

      DROP PROC dbo.JobStatus

GO

CREATE PROC [dbo].[JobStatus]

      @IncludeIdle bit =      0

AS

/*    DATE        AUTHOR            REMARKS

      6/14/12           PPaiva            Initial creation.

      DESCRIPTION

            Poplulates a global temp table with all jobs, with Status.

      DEPENDENCIES

            master.dbo.xp_sqlagent_enum_jobs

            Admin.dbo.udfFormatTimeWithColons()

            Admin.dbo.vwJob

           

      DEBUG

            SELECT *

            FROM Admin..infraJobState

           

            SELECT *

            FROM msdb..sysjobactivity

      USAGE

            Exec Adminep..JobStatus      -- Only active jobs

            Exec Adminep..JobStatus 1    -- All jobs

            SELECT *

            FROM ##JobStatus

            ORDER BY 1

*/

SET NOCOUNT ON

IF Object_ID('tempdb.dbo.##JobStatus') Is Not Null

      DROP TABLE ##JobStatus

CREATE TABLE #JobState(

      job_id uniqueidentifier not null,

      last_run_date int not null,

      last_run_time int not null,

      next_run_date int not null,

      next_run_time int not null,

      next_run_schedule_id int not null,

      requested_to_run int not null, -- bool

      request_source int not null,

      request_source_id sysname collate database_default null,

      running int not null, -- bool

      current_step int not null,

      current_retry_attempt int not null,

      job_state int not null

      )

INSERT INTO #JobState

      Exec master.dbo.xp_sqlagent_enum_jobs

                  @is_sysadmin = 1,

                  @job_owner = ''

SELECT  i.Description Status,

            j.Server,

            j.Name,

            j.JobEn,

            j.SchedEn,

            js.request_source_id RequestedBy,

            js.current_step CurrStep,

            Convert(varchar(16),

                  CASE WHEN IsNull(JS.Last_Run_Date, 0) = 0 THEN Null

                         ELSE Convert(datetime,

                                          Convert(varchar, JS.Last_Run_Date) +

                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, JS.Last_Run_Time), 6))

                                                      )

                        END, 120) LastRunDateTime,         

            j.NextRun,

            j.NextRunDateTime,

            j.Category,

            j.SchedName,

            j.Next_Run_Date,

            j.Next_Run_Time,

            j.JobCreated,

            j.JobModified,

            j.Description,

            j.Job_ID,

            j.OrderByMe,

            js.running

INTO ##JobStatus

FROM #JobState js

JOIN Admin.dbo.vwJob j

      ON j.job_id = js.job_id

JOIN Admin.dbo.infraJobState i

      ON i.ID = js.job_state

ORDER BY running desc

IF @IncludeIdle  = 1

      SELECT *

      FROM ##JobStatus

      ORDER BY 1

ELSE

      SELECT *

      FROM ##JobStatus

      WHERE Status <> 'Idle'

      ORDER BY 1

Print '-- All

SELECT *

FROM ##JobStatus

WHERE Name Like ''%%''

ORDER BY 1 desc

-- Exclude Idle

SELECT *

FROM ##JobStatus

WHERE Status <> ''Idle''

ORDER BY 1

'