如何在SQL Server维护计划中捕获详细错误信息并发送通知？

我正在开发SQL Server维护计划，需要在作业失败时捕获详细错误信息并发送通知邮件。已创建发送邮件的存储过程，但无法在通知中获取详细错误信息，以下是当前配置：

用于发送通知的存储过程 #

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SendJobFailureNotification]
(
    @JobName NVARCHAR(128),
    @ErrorMessage NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @Body NVARCHAR(MAX);
    SET @Body = N'The job ' + @JobName + N' has failed with the following error: ' + @ErrorMessage + CHAR(13) + CHAR(10) +
                'Error Number: ' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                'Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                'State: ' + CAST(ERROR_STATE() AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                'Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A') + CHAR(13) + CHAR(10) +
                'Line Number: ' + CAST(ERROR_LINE() AS NVARCHAR(10));

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'BackupAlertsProfile',
        @recipients = 'your.email@example.com',
        @subject = 'SQL Server Job Failure',
        @body = @Body;
END;
GO

带错误处理的作业步骤 #

BEGIN TRY
    -- Start the maintenance plan job
    EXEC msdb.dbo.sp_start_job @job_name = N'TestMaintenancePlan.Subplan_1'; 
END TRY
BEGIN CATCH
    -- Log detailed error information and send notification
    DECLARE @ErrorMessage NVARCHAR(MAX);
    SET @ErrorMessage = ERROR_MESSAGE();
    
    -- Calling the stored procedure to send a failure notification email
    EXEC dbo.SendJobFailureNotification @JobName = N'TestMaintenancePlan.Subplan_1', @ErrorMessage = @ErrorMessage;
    
    -- Raise the error again to ensure the job shows as failed
    RAISERROR('Job Failed: %s', 16, 1, @ErrorMessage);
END CATCH;

问题原因及修复方案 #

问题根源 #

ERROR_NUMBER()、ERROR_SEVERITY()这类错误函数仅在当前错误上下文中有效。当从CATCH块调用存储过程时，已经脱离了原错误的上下文，这些函数会返回NULL或无效值，导致邮件无法获取详细错误信息。

修复后的存储过程 #

修改存储过程，将所有错误字段作为参数传入：

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SendJobFailureNotification]
(
    @JobName NVARCHAR(128),
    @ErrorMessage NVARCHAR(MAX),
    @ErrorNumber INT,
    @ErrorSeverity INT,
    @ErrorState INT,
    @ErrorProcedure NVARCHAR(128),
    @ErrorLine INT
)
AS
BEGIN
    DECLARE @Body NVARCHAR(MAX);
    SET @Body = N'作业 ' + @JobName + N' 执行失败，错误信息如下：' + CHAR(13) + CHAR(10) +
                '错误内容：' + @ErrorMessage + CHAR(13) + CHAR(10) +
                '错误编号：' + CAST(@ErrorNumber AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                '严重级别：' + CAST(@ErrorSeverity AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                '错误状态：' + CAST(@ErrorState AS NVARCHAR(10)) + CHAR(13) + CHAR(10) +
                '出错存储过程：' + ISNULL(@ErrorProcedure, 'N/A') + CHAR(13) + CHAR(10) +
                '出错行号：' + CAST(@ErrorLine AS NVARCHAR(10));

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'BackupAlertsProfile',
        @recipients = 'your.email@example.com',
        @subject = 'SQL Server 作业执行失败通知',
        @body = @Body;
END;
GO

修复后的作业步骤 #

在CATCH块中先捕获所有错误信息，再传入存储过程：

BEGIN TRY
    -- 启动维护计划作业
    EXEC msdb.dbo.sp_start_job @job_name = N'TestMaintenancePlan.Subplan_1'; 
END TRY
BEGIN CATCH
    -- 捕获所有详细错误信息
    DECLARE @ErrorMessage NVARCHAR(MAX),
            @ErrorNumber INT,
            @ErrorSeverity INT,
            @ErrorState INT,
            @ErrorProcedure NVARCHAR(128),
            @ErrorLine INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE();
    
    -- 调用存储过程发送包含完整错误信息的通知邮件
    EXEC dbo.SendJobFailureNotification 
        @JobName = N'TestMaintenancePlan.Subplan_1',
        @ErrorMessage = @ErrorMessage,
        @ErrorNumber = @ErrorNumber,
        @ErrorSeverity = @ErrorSeverity,
        @ErrorState = @ErrorState,
        @ErrorProcedure = @ErrorProcedure,
        @ErrorLine = @ErrorLine;
    
    -- 重新抛出错误，确保作业标记为失败状态
    RAISERROR('作业执行失败：%s', 16, 1, @ErrorMessage);
END CATCH;

额外说明 #

• 确保SQL Server代理已配置并启用BackupAlertsProfile邮件配置文件
• 若维护计划子作业包含多步骤，建议在子作业的每个步骤单独添加错误处理，或结合SQL Server代理的作业日志查询获取更精准的错误节点信息

内容的提问来源于stack exchange，提问作者Abuti