1

I am adding an alert when a user logs into an application, if the Microsoft SQL database maintenance plan failed, did not run, was not implemented OR was never scheduled. I would prefer not to add a setting to identify the maintenance plans which should be checked.

How can I get a list of maintenance plans which perform any action on the current database "SELECT db_name()"?

I can see the list of all maintenance plans here: SELECT * FROM msdb.dbo.sysmaintplan_plans

I'm guessing that I'll need to query the plan steps and check if any contain my database.

It would be nice to be able to identify the type of each step, ie: Integrity check, Index rebuild, statistics update or backup, etc... Then I could ensure all the required steps are being accomplished.

Moon
  • 1,141
  • 2
  • 11
  • 25

1 Answers1

1

By using the SQL profiler, I gathered several queries and put together this single SQL together which will show the log history of any maintenance plan that includes the current database. It shows each step and if it succeeded.

SELECT
    ld.server_name,
    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],
    ld.start_time AS [StepStart],
    ld.end_time AS [StepEnd],
    ld.succeeded  AS [StepSucceeded],
    ld.line1,
    ld.line2,
    ld.line3,
    ld.line4,
    ld.line5,
    ld.command
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
    INNER JOIN msdb.dbo.sysmaintplan_logdetail ld ON mpl.task_detail_id = ld.task_detail_id
        AND ld.command LIKE ('%['+db_name()+']%')
ORDER BY
    mpl.start_time DESC

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

Moon
  • 1,141
  • 2
  • 11
  • 25