Maintenance Plan history

I have been researching for the last couple of weeks a way to acquire a maintenance plan job history, success or failure.

After alot of searching and finding that no one has or is not sharing the ability to do this, I decided to start hacking away at the MS SQL 2005/2008 core databases to find the relationship.

After much digging, there are a total of 4 tables you need to have access to, whether through a proxy account of dbo schema account.

Tables in question:
- msdb..sysmaintplan_log
- msdb..sysmaintplan_logdetail
- msdb..sysmaintplan_subplans
- msdb..sysjobs (optional: gives the SQL Agent Job name)

SQL Script as i built it, but you will probably modify it to your needs:


SELECT
mpld.server_name,
j.name,
mpl.start_time,
mpl.end_time,
mpl.succeeded,
mpld.error_number,
mpld.error_message,
mpld.line1 + char(10) + char(13) +
mpld.line2 + char(10) + char(13) +
mpld.line3 + char(10) + char(13) +
mpld.line4 + char(10) + char(13) +
mpld.line5 as details
FROM msdb..sysmaintplan_log mpl
inner join msdb..sysmaintplan_logdetail mpld on mpl.task_detail_id = mpld.task_detail_id
inner join msdb..sysmaintplan_subplans mpsp on mpl.subplan_id = mpsp.subplan_id
inner join msdb..sysjobs j on mpsp.job_id = j.job_id
where mpl.start_time between dateadd(day, -7, '5/2/2011') and '5/2/2011'
order by mpl.end_time desc


As you can see, this only shows the logdetail entries from (5/2/2011 -7days) to 5/2/2011, which is in effect a range from 4/25/2011 00:01 (or 00:00:001) to 5/1/2011 00:00.