主页 / dba / 问题 / 4734
Accepted
Nick Chammas
Asked: 2011-08-18 08:18:24 +0800 CST

当作业失败时，如何让 SQL Server 通过电子邮件向我发送错误详细信息？

  • 772

SQL Server 允许您将作业配置为在失败时发送电子邮件警报。这是监控您的工作的一种简单而有效的方法。但是，这些警报不包括任何细节——只是成功或失败通知。

如果作业失败，典型的警报电子邮件将如下所示：

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before 
                Backup 12AM).  The last step to run was step 1 (Check Databases).

要确定失败的原因，您必须导航到 SQL Server Management Studio 中的实例，找到作业并查看其执行历史记录。在大型环境中，必须不断地执行此操作可能会很痛苦。

理想的警报电子邮件会预先包含故障原因，让您直接着手解决问题。

我熟悉这个问题的解决方案。有没有人有这方面的经验？它的缺点是：

  1. 您必须为每项工作添加一个新步骤，并且
  2. 你必须祈祷没有人搞砸警报过程，spDBA_job_notification

有没有人提出更好的解决方案？

sql-server monitoring

4 个回答

  1. Best Answer

    你可能会做的事情只是一个想法，把想法扔出去......

    创建一个定期检查 msdb 中的作业表的作业，以查看是否有任何作业显示为失败，这可以通过良好的T-SQL 查询来完成。然后您可以进入 sysjobsteps 表并查看是否为作业设置了输出日志。让存储过程发送一封电子邮件，并附上该文件。您将能够准确地看到作业从开始到失败的过程，而无需接触服务器。

    然后还可以让 PowerShell 脚本检查事件日志中的错误。它允许您过滤掉相当多的信息，以准确获取您正在寻找的消息类型。您可以将其设置为定期运行的 SQL 代理作业。然后在 PowerShell 脚本中使用 email cmdlet 发送消息（如果找到）。

    这里的想法很牵强，只是我想到的一些。

    • 10

  2. 我有上述想法的经验。这很好，但更好的主意是像肖恩所说的那样做。

    我们所做的是创建一个每 5 分钟运行一次的作业，并扫描 MSDB 表以了解作业失败。对于每个失败的作业，我们将使用它自己的 ID 运行 SP spDBA_job_notification，因此 SP 将扫描 MSDB 历史步骤中的错误并通过电子邮件将它们全部发送出去。来自 SP 文档：“存储过程使用作业 ID 查询 msdb 代理表以获取该作业的最新错误消息。”

    因此，与其只改变每一项工作，不如创建一个可以完成所有工作的单一工作;-)。

    另一个想法是将所有作业设置为在出现错误/失败的情况下写入 Windows 事件查看器，并使用扩展的 proc xp_ReadErrorLog或自动工具从那里读取（如果您的网络中已有该工具）。例如，我们使用HPOV检查任何系统问题，并可以为所有事件查看器错误配置一个简单的警报（不需要任何自定义作业或过程）。

    • 3

  3. 试一试，只需在 TSQL 中根据需要插入变量。这里的关键是将其作为每个单独的 SQL 代理作业的最后一步，但是上面的每个作业步骤都需要转到下一步，无论是失败还是成功……在大多数情况下对我来说都很好，但请请报告您遇到的任何问题。我们在 SQL Server 2008 R2 上，所以这是我目前设置它的地方。

    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
BEGIN
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLServer Alerts'
DECLARE @emailrecipients NVARCHAR(500) = '[email protected]'
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

IF EXISTS (
        SELECT *
        FROM #TempJobFailRpt
        )
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<body>
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
            ****** 
            Failure occurred in the SQL Agent job named: ''' + @job_name + ''' in at least one of the steps. 
            Below is the job failure history detail for ALL runs of this job today without needing to connect to SSMS to check.
            ******
        </caption>

<tr>
    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = run_date
                ,''
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
END
END
    • 2

  4. 这个非常老的问题的另一个答案，这个存储过程似乎工作得很好。

    https://karaszi.com/mailafterjob-send-email-after-agent-job-include-ouput-files

    Tibor 包含了对存储过程、参数描述和示例的精彩总结。

    • -1

相关问题