SQL Server维护计划历史记录检查成功或失败 [英] SQL Server Maintenance plan history check for success or failure

问题描述

当用户登录到连接到Microsoft SQL Server数据库的应用程序时，我想添加一条警报，以检查其夜间维护计划是否失败或没有运行.

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.

使用SQL，如何检查给定的维护计划上次运行的时间，以及该计划成功还是失败?

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.

推荐答案

根据Nathans的建议，我运行了SQL事件探查器，并将这些查询编译为一个查询以适合我的需求.

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.

这篇关于SQL Server维护计划历史记录检查成功或失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！