[SOLVED] SQL Server Maintenance plan history check for success or failure

SQL Server Maintenance plan history check for success or failure

When users log into a my application which connects to a Microsoft SQL Server database, I want to add an alert which checks if their nightly maintenance plan failed or did not run.

Using SQL, how can I check when a given maintenance plan was last run and if it succeeded or failed?

I can see the maintenance plans in:

SELECT * 
FROM msdb.dbo.sysmaintplan_plans

however I am not sure which tables to join that contain the history.

Solution

Per Nathans suggestion, I ran the SQL profiler and compiled those queries into a single one to suit my needs.

This query will give the status of the most recent run of any maintenance plan which includes the current database:

SELECT
    mp.name AS [MTX Plan Name],
    msp.subplan_name AS [Sub Plan Name],    
    mpl.start_time AS [JobStart],
    mpl.end_time AS [JobEnd],
    mpl.succeeded AS [JobSucceeded]
FROM
    msdb.dbo.sysmaintplan_plans mp
    INNER JOIN msdb.dbo.sysmaintplan_subplans msp ON mp.id = msp.plan_id
    INNER JOIN msdb.dbo.sysmaintplan_log mpl ON msp.subplan_id = mpl.subplan_id
        AND mpl.task_detail_id = -- Get the most recent run for this database
            (SELECT TOP 1 ld.task_detail_id 
            FROM msdb.dbo.sysmaintplan_logdetail ld
            WHERE ld.command LIKE ('%['+db_name()+']%')
            ORDER BY ld.start_time DESC)

This works best with maintenance plans generated by the wizard. Ad-hoc plans do not always include the command for filtering. But the table linking still works.