如何通过脚本从SQL Server代理获取失败的作业？

Question

我希望通过SQL脚本获取SQL Server代理无法启动或运行的失败作业...

- Talha

Answer 1

我创建了一个视图来获取 SQL Server 中的失败作业。

CREATE VIEW dbo.View_Failed_Jobs
    AS
    SELECT   Job.instance_id
        ,SysJobs.job_id
        ,SysJobs.name as 'JOB_NAME'
        ,SysJobSteps.step_name as 'STEP_NAME'
        ,Job.run_status
        ,Job.sql_message_id
        ,Job.sql_severity
        ,Job.message
        ,Job.exec_date
        ,Job.run_duration
        ,Job.server
        ,SysJobSteps.output_file_name
    FROM    (SELECT Instance.instance_id
        ,DBSysJobHistory.job_id
        ,DBSysJobHistory.step_id
        ,DBSysJobHistory.sql_message_id
        ,DBSysJobHistory.sql_severity
        ,DBSysJobHistory.message
        ,(CASE DBSysJobHistory.run_status
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Canceled'
            WHEN 4 THEN 'In progress'
        END) as run_status
        ,((SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 5, 2) + '/'
        + SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 7, 2) + '/'
        + SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 1, 4) + ' '
        + SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS varchar)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 1, 2) + ':'
        + SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS VARCHAR)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 3, 2) + ':'
        + SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time as varchar)))
        + CAST(DBSysJobHistory.run_time AS VARCHAR)), 5, 2))) AS 'exec_date'
        ,DBSysJobHistory.run_duration
        ,DBSysJobHistory.retries_attempted
        ,DBSysJobHistory.server
        FROM msdb.dbo.sysjobhistory DBSysJobHistory
        JOIN (SELECT DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ,MAX(DBSysJobHistory.instance_id) as instance_id
            FROM msdb.dbo.sysjobhistory DBSysJobHistory
            GROUP BY DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
        WHERE DBSysJobHistory.run_status <> 1
        ) AS Job
    JOIN msdb.dbo.sysjobs SysJobs
       ON (Job.job_id = SysJobs.job_id)
    JOIN msdb.dbo.sysjobsteps SysJobSteps
       ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)
    GO

-- 现在执行select语句来查询失败的任务

Select * from View_Failed_Jobs

- Talha

---

需要注意的是，此脚本仅列出上次执行失败的作业，并仅列出最后一次失败。如果随后成功，则此脚本根本不显示它。这可能是您想要的，也可能不是。 - paulH

Answer 2

以下是SQL Server 2008的示例：
SQL Server代理作业查询示例。

如果搜索"sql server failed jobs"，会发现更多相关内容，但不幸的是，大多数示例并没有说明它们是否适用于SQL Server 2008，例如这个示例。

编辑提示：从第一个链接中复制代码时，请使用"查看源代码"按钮：。还有一个"复制到剪贴板"按钮，我先尝试了一下，但它在SQL代码中生成了一些HTML标签，导致SQL Server出错，无法执行查询。

- Christian Specht

---

链接腐败。这里是SQL Server Agent Job Query Samples的更新链接。 - superEb

内容也会腐烂。一些查询结果与预期不符。 - wp78de

Answer 3

您可以使用 SQL Agent 快捷方式——>作业活动监视器。

- Vishe