Maintenance Plan - Get Job Run Detail


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


Author:        ChillyDBA
Date:            23 Jul 2012


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. 


Stored Procedure:

DROP PROC usp_GetMaintPlanJobRunDetails


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.

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

Andy Hughes,
Jul 24, 2012, 10:47 AM