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.

Sunday, June 10, 2012

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

'





No comments:

Post a Comment