MSDB - Get MSDB Job Last Run Summary

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 SQLAgent scheduled jobs, regardless of their type and active status.  
The list is augmented with last run status, duration, message and notification data.

 
** 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_GetMaintPlanJobLastRunSummary
GO

CREATE PROC usp_GetMaintPlanJobLastRunSummary
AS

/****************************************************
Purpose:   To list all SQL Maintenance Plan jobs
           scheduled using SQL Agent and
           the last run details if they exist.
          
           Utilised the following 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:  This SP is basically the usp_GetMSDBJobLastRunSummary sp with an additional join
           via the sysmaintplan_subplans table to screen out all non-MaintPlan jobs.
          
Author:        ChillyDBA
History:   23 July 2012 - Intial Issue
*****************************************************/

;WITH LastRun AS
(
  
SELECT
      
jh.job_id               AS Job_ID,
      
MAX(jh.instance_id)     AS Instance_ID
  
FROM msdb.dbo.sysjobhistory jh (NOLOCK)
  
WHERE jh.step_id = 0  --'(Job OutCome)'
  
GROUP BY jh.job_id
)
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.sql_message_id                               AS JobSQLMessageID,
  
jh.sql_severity                                 AS JobSQLSeverity,
  
jh.MESSAGE                                      AS JobMessage,
  
dbo.udf_IntToDateTime(jh.run_date, jh.run_time) AS JobLastRunDate,
  
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,
  
soe.name                                        AS OperatorEmailed,
  
son.name                                        AS OperatorNetSent,
  
sop.name                                        AS OperatorPaged,
  
jh.server                                       AS ServerName
FROM   dbo.udf_GetMSDBJobList (NULL, 'A') jobs

-- NOTE:  we can join directly to subplans as this is the first table in the maintplan storage
-- that contains a link to SQLAgent jobs.  This ensures only MaintPlan jobs are returned in the results
INNER JOIN msdb.dbo.sysmaintplan_subplans sp
  
ON jobs.job_id = sp.job_id
  
LEFT OUTER JOIN  LastRun lr
  
ON jobs.job_id = lr.job_id
LEFT OUTER JOIN  msdb.dbo.sysjobhistory jh (NOLOCK)
  
ON lr.job_id = jh.job_id
  
AND lr.Instance_ID = jh.Instance_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

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