如果作业中的任何步骤失败，请通知操作员。

Question

如何配置Sql Server 2008，使其在作业中的任何步骤失败时通知操作员？

我有一个Sql Server作业，其中包含多个步骤来从不同来源更新数据，最后一个步骤对数据执行多项计算。所有“数据刷新”步骤都设置为“在失败时转到下一步”。通常情况下，如果其中一个“数据刷新”步骤失败了，我仍希望最后一个步骤运行，但我仍希望被通知中间的失败，以便如果它们一直失败，我可以进行调查。

- Matt Murrell

Answer 1

这是我们的做法。我们添加一个最后的T-SQL步骤（通常称为“检查步骤”），其中包括以下内容：

SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
        RAISERROR('Ooops', 16, 1)

请注意，此代码使用作业步骤中的tokens（即 $(...) 部分），因此代码不能像在SSMS中一样执行。它基本上尝试查找当前作业先前步骤的条目，并查找失败状态。

在“属性” - >“高级”中，您还可以检查将步骤输出包括在历史记录中以获取步骤失败的消息。将 On failure action 保留为 Quit the job reporting failure 。

- wqw

---

漂亮而巧妙。我将其扩展为不那么漂亮但有用的东西；请看我的答案。 - Adamantish

Answer 2

@wqw的答案很出色。

我在此基础上进行了扩展，对于启用了数据库邮件的人们，可以通过电子邮件发送更多有关失败原因和详细信息。同时还结合了icvader在本页上的答案，以考虑重试。

对于我们这些需要更多详细信息来判断离线/在呼时是否需要紧急采取行动的人来说，这应该非常有帮助。

DECLARE 

@YourRecipients as varchar(1000) = '[e-mail]'
,@YourMailProfileName as varchar(255) = 'Database Mail'

,@Msg as varchar(1000)
,@NumofFails as smallint
,@JobName as varchar(1000)
,@Subj as varchar(1000)
,@i as smallint = 1


---------------Fetch List of Step Errors------------
SELECT *
INTO #Errs

FROM

    (
    SELECT 
      rank() over (PARTITION BY step_id ORDER BY step_id) rn
    , ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder
    ,j.name job_name
    ,run_status
    , step_id
    , step_name
    , [message]

    FROM    msdb.dbo.sysjobhistory h
    join msdb.dbo.sysjobs j on j.job_id = h.job_id

    WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                    WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
            AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
    ) as agg

WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
  AND run_status <> 1 -- show only those that didn't succeed 


SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.


-------------------------If there are any failures assemble email and send ------------------------------------------------
IF  @NumofFails <> 0
    BEGIN

        DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END ---To make it look like a computer knows English
        SELECT top 1 @Subj = 'Job: ' + job_name + ' had ' + CAST(@NumofFails as varchar(3)) + ' step' + @PluralS + ' that failed'
                    ,@Msg =  'The trouble is... ' +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)

                        FROM dbo.#Errs


        WHILE @i <= @NumofFails 
        BEGIN
            SELECT @Msg = @Msg + 'Step:' + CAST(step_id as varchar(3)) + ': ' + step_name  +CHAR(13) + CHAR(10)

            + [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10) FROM dbo.#Errs
            WHERE rn = @i


            SET @i = @i + 1
        END

            exec msdb.dbo.sp_send_dbmail
            @recipients = @YourRecipients,
            @subject = @Subj,
            @profile_name = @YourMailProfileName,
            @body = @Msg


    END

与其它答案的一个不同之处：不会将整个作业视为错误。 这是为了保留作业历史中“已终止”和“带有错误完成”的区别。

- Adamantish

Answer 3

以下是对上述答案的改进，如果有人想要在SQL Server代理中使用运算符发送电子邮件，并使用存储在MSDB中的数据库配置文件名称：

DECLARE @EmailRecipients as varchar(1000)
DECLARE @MailProfileName as varchar(255)
DECLARE @Msg as varchar(1000)
DECLARE @NumofFails as smallint
DECLARE @JobName as varchar(1000)
DECLARE @Subj as varchar(1000)
DECLARE @i as smallint = 1

SELECT @EmailRecipients = email_address 
FROM msdb.dbo.sysoperators
WHERE name = <Operator Name>

SELECT TOP(1) @MailProfileName = name 
FROM msdb.dbo.sysmail_profile

SELECT * INTO #Errs
FROM
    (SELECT rank() over (PARTITION BY step_id ORDER BY step_id) rn, 
            ROW_NUMBER() over (partition by step_id order by run_date desc, run_time desc) ReverseTryOrder,
           j.name job_name,
           run_status,
           step_id,
           step_name,
           [message]
     FROM msdb.dbo.sysjobhistory h
     JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
     WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                    WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
     AND h.job_id = $(ESCAPE_SQUOTE(JOBID))
    ) AS agg
WHERE ReverseTryOrder = 1 ---Pick the last retry attempt of each step
AND run_status <> 1 -- show only those that didn't succeed 


SET @NumofFails = ISNULL(@@ROWCOUNT,0)---Stored here because we'll still need the rowcount after it's reset.

IF  @NumofFails <> 0
BEGIN
    DECLARE @PluralS as char(1) = CASE WHEN @NumofFails > 1 THEN 's' ELSE '' END

    SELECT top 1 @Subj = job_name + ':'+ CAST(@NumofFails as varchar(3)) + '''Check Steps'' Report',
                 @Msg =  '''Check Steps'' has reported that one or more Steps failed during execution of ' + job_name + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
    FROM dbo.#Errs

    WHILE @i <= @NumofFails 
    BEGIN
        SELECT @Msg = @Msg + 'Step ' + CAST(step_id as varchar(3)) + ': ' + step_name  +CHAR(13) + CHAR(10)
                     + [message] +CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10) 
        FROM dbo.#Errs
        WHERE rn = @i

        SET @i = @i + 1
    END

    EXEC msdb.dbo.sp_send_dbmail
    @recipients = @EmailRecipients,
    @subject = @Subj,
    @profile_name = @MailProfileName,
    @body = @Msg
END

- Mamta Kaushik

---

我还没有仔细研究过这个问题，但我可以说一句话。它的样式比我以前写SQL语句的方式要好得多。 - Adamantish

Answer 4

由于独特的事务日志场景偶尔会导致阻塞，我将大多数步骤设置为重试。即使步骤已经成功重试，wqw的帖子也会发出警报。我进行了一些调整，如果步骤失败，但在重试后成功了，则不会发出警报。

SELECT  step_id, MIN(run_status)
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
GROUP BY step_id
HAVING MIN(run_status) <> 1 -- success

IF @@ROWCOUNT <> 0
RAISERROR('FailedStep', 16, 1)

- icvader

Answer 5

Adamantish的回答是完美的解决方案（谢谢）：运行无误，只需进行少量编辑。正如wqw之前所述，在SSMS中不起作用，请将其作为最后一步添加并运行该作业。

WHERE instance_id > COALESCE
(
(
SELECT MAX(instance_id) 
FROM msdb.dbo.sysjobhistory
WHERE job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX' AND step_id = 0), 0
)
AND h.job_id = '2XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXX'
) 
as agg

- s6a6n6d6m6a6n

Answer 6

进入作业属性 > 通知选项卡 > 作业完成时执行的操作

在此处勾选电子邮件复选框，并从下拉列表中选择“当作业失败时”，然后保存作业。

请阅读http://msdn.microsoft.com/en-us/library/ms191130.aspx上的第4点。

如果您想通过电子邮件通知操作员，请勾选电子邮件，从列表中选择一个操作员，然后选择以下之一：

• 当作业成功时：在作业成功完成时通知操作员。

• 当作业失败时：在作业未能成功完成时通知操作员。

• 当作业完成时：无论完成状态如何都通知操作员。

- Thakur

---

不幸的是，这并没有解决问题。首先，无论成功或失败，它都会在完成后发送电子邮件。我希望只有在步骤失败时才会收到电子邮件。此外，完成邮件中没有包含任何指示特定步骤失败的文本。它只是简单地声明“作业成功”。我该如何知道哪个步骤失败了呢？ - Matt Murrell

使用此选项“当作业失败时”通知操作员，以便在作业未成功完成时得到通知。这意味着如果作业完成时有任何失败的步骤（即使对于作业步骤指定了出错转到下一步），您也会收到通知。然后，您可以查看作业历史记录以获取更多详细信息。怎么样！ - Thakur

“当作业失败时”的选项不会发送电子邮件，因为最后一步成功了（也称为“退出报告成功的作业”）。 - Matt Murrell

Answer 7

在每个步骤中添加代码：

if @@error > 0
EXEC sp_send_dbmail @profile_name='DBATeam',
@recipients=[e-mail]',
@subject='SomeJob SomeStep failed',
@body='This is the body of SomeJob SomeStep failed' 

- GLeb