Maintenance Plan - Get Job Run Detail

Applicability:

                 SQL Server 2000:        Not Suported**
                 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 runs of a specified SQL Server Maintenance Plan scheduled job

Due to the fuzziness of the relationship between the data for Jobs and Maintenance Plans in the MSDB database, the Jobs portion of the data (including extended logging details) has been omitted from this code.   I haven't really yet found a way to reliably join the 2 sets of 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_GetMaintPlanJobRunDetails
GO

CREATE PROC usp_GetMaintPlanJobRunDetails (@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)
          
           
Author:        ChillyDBA
History:   22 July 2012 - Intial Issue
           24 July 2012 - ChillyDBA - removed the joins to the udf_GetMSDBJobList TVF
           as the joining between Jobs and Plans executions is a little fuzzy - the plan
           history structure has subplan_id and task_detail_id as the keys, whereas jobs have
           job_id and step_uid. Had to settle for just getting all the task details related to one
           job_id.  There is no real method of retrieving the extended logging for a maintplan task
           that I could find.
*****************************************************/


SELECT
  
sp.subplan_name                                 AS SubPlanName,
  
pld.line1                                       AS SubPlanTaskName,
  
pld.line2                                       AS PlanTaskDescription2,
  
pld.line3                                       AS PlanTaskDescription3,
  
pld.line4                                       AS PlanTaskDescription4,
  
pld.line5                                       AS PlanTaskDescription5,
  
pld.Start_Time                                  AS PlanTaskStartTime,
  
pld.End_Time                                    AS PlanTaskEndTime,
  
DATEDIFF(ss, pld.Start_Time, pld.End_Time)      AS PlanTaskDuration,
  
CASE pld.succeeded
      
WHEN 1 THEN 'Succeeded'
      
ELSE 'Failed'
  
END                                             AS PlanTaskStatus,
  
pld.error_number                                AS PlanErrorNumber,
  
pld.error_message                               AS PlanErrorMessage
FROM   msdb.dbo.sysmaintplan_subplans sp
INNER JOIN  msdb.dbo.sysmaintplan_log pl
  
ON sp.subplan_id = pl.subplan_id
INNER JOIN  msdb.dbo.sysmaintplan_logdetail pld
  
ON pl.task_detail_id = pld.task_detail_id
WHERE sp.job_id = @job_id
ORDER BY
  
pld.Start_Time,
  
pld.line1

ċ
usp_GetMaintPlanJobRunDetails.sql
(2k)
Andy Hughes,
Jul 24, 2012, 10:47 AM
Comments