MSDB - Get MSDB Job Run Details

Applicability:

                 SQL Server 2000:        Not Supported**
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        23 Jul 2012

Description

Retrieves a list of all run of a specified SQLAgent scheduled job
The list is augmented with last run status, duration, message and notification data for all steps in the job.
It must be noted that Step 0 ('Job Outcome') will always be present as a system-generated step and contains a summary of job execution information.  There will then be one or more additional step details for the job, depending on how many steps the job has and how many ran before the job completed or gave an error.

 
** NOTE:  This code was written for SQL 2005 and greater.  There are slightly different views for obtaining data from SQL 2000, or for obtaining data in SQL 2005+ that has been subjected to an upgrade from SQL 2000.  While upgrading maintenance plans generally works, I have always found it beneficial in the long term to recreate them from scratch.  The feature set is better, and there are some tasks that are not 100% happy working across editions.  I have therefore not included versions of the code that support SQL 2000 version maintenance plans.

Code

Stored Procedure:


DROP PROC usp_GetMSDBJobRunDetails
GO

CREATE PROC usp_GetMSDBJobRunDetails (@Job_ID UNIQUEIDENTIFIER)
AS

/****************************************************
Purpose:   To list a single SQL Agent scheduled job and
           the historic run details if they exist.
          
           The Job_ID passed as a parameter determines the job
           to be displayed.
           Job_ID was used ratehr than name as the name can be changed
           through the GUI but the Job_ID remains the same,
           and it was originally intended that this SP be called
           from an SSRS report using click-through to a sub-report (so no need
           to actually type the GUID)
          
           Utilised the followng function(s) to retrieve
           results and/or to enhance readability:
          
               dbo.udf_GetMSDBJobList - gets a basic set of job details
               dbo.udf_IntToDateTime - converts the MSDB date and time integers into a datetime variable
              
           NOTE:  All steps (0..n) will be return for each job, as there may be several combinations of results type
           which would be complex to code for, but which would make sense if displayed as a report grouping
          
               1.  Job failed on invocation - only step #0 with fail status
               2.  Job failed during invocation - steps 0..n with fail status for steps #0 and #n and success for steps in between
               3.  Job succeeded - steps 0..n all with succeeded status
              
           Unfortunately, step #0 contains summary information - for single-step jobs, step #0 and #1 appear to have duplicate info.
          
Author:        ChillyDBA
History:   22 July 2012 - Intial Issue
*****************************************************/

SELECT
  
jobs.Job_ID                                     AS Job_ID,
  
jobs.JobName                                    AS JobName,
  
jobs.Category                                   AS JobCategory,
  
jobs.Enabled                                    AS JobEnabled,
  
jobs.Scheduled                                  AS JobScheduled,
  
jobs.Description                                AS JobDescription,
  
jobs.Occurs                                     AS JobOccurs,
  
jobs.OccursDetail                               AS JobOccursDetail,
  
jobs.Frequency                                  AS JobFrequency,
  
jobs.Duration                                   AS JobDuration,
  
jobs.JobValidUntil                              AS JobValidUntil,
  
jh.step_id                                      AS JobStepID,
  
dbo.udf_IntToDateTime(jh.run_date, jh.run_time) AS JobLastRunDate,
  
jh.run_duration                                 AS JobLastRunDuration,
  
CASE jh.run_status
      
WHEN 0 THEN 'Failed'
      
WHEN 1 THEN 'Succeeded'
      
WHEN 2 THEN 'Retry'
      
WHEN 3 THEN 'Cancelled'
  
END                                             AS JobLastRunStatus,
  
jh.sql_message_id                               AS JobSQLMessageID,
  
jh.sql_severity                                 AS JobSQLSeverity,
  
jh.MESSAGE                                      AS JobMessage,
  
soe.name                                        AS OperatorEmailed,
  
son.name                                        AS OperatorNetSent,
  
sop.name                                        AS OperatorPaged,
  
jh.server                                       AS ServerName
FROM   dbo.udf_GetMSDBJobList (NULL, 'A') jobs
INNER JOIN  msdb.dbo.sysjobhistory jh (NOLOCK)
  
ON jobs.job_id = jh.job_id
LEFT OUTER JOIN msdb..sysoperators soe
  
ON jh.operator_id_emailed = soe.id
LEFT OUTER JOIN msdb..sysoperators son
  
ON jh.operator_id_netsent = son.id
LEFT OUTER JOIN msdb..sysoperators sop
  
ON jh.operator_id_paged = sop.id
WHERE jobs.job_id = @job_id
ORDER BY jh.instance_id DESC  -- list the runs from the most recent to the least recent

ċ
usp_GetMSDBJobRunDetails.sql
(3k)
Andy Hughes,
Jul 23, 2012, 10:27 AM
Comments