2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Moon
  • 1,141
  • 2
  • 11
  • 25
  • msdb..sysdbmaintplan_history – Serpiton Nov 22 '15 at 19:28
  • That table "msdb..sysdbmaintplan_history" is empty. However when I view the history of the maintenance plan using SQL management studio it clearly shows a long history of the success's and failures of the maintenance plans. – Moon Nov 22 '15 at 19:38
  • Run a profiler trace when you view the history and you'll get your query – nathan_jr Nov 23 '15 at 00:33

1 Answers1

3

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.

Moon
  • 1,141
  • 2
  • 11
  • 25