SQL Server 2012 SQL Agent作业失败通用实时处理方案咨询

你不需要逐个修改200个现有作业的配置，以下两个方案都是事件驱动主动触发，完全满足实时性要求，零侵入现有作业逻辑：

方案1：基于Service Broker事件通知（推荐，纯数据库侧配置，无需额外部署程序） #

这个方案是直接订阅SQL Server实例层面的作业失败系统事件，作业执行失败的瞬间会主动推送事件到你配置的处理逻辑，没有任何轮询延迟，现有作业的原有通知、步骤跳转逻辑完全不受影响。
实施步骤：

• 先确认MSDB库的Service Broker已启用，在SSMS中执行以下语句检查：

  SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
  

  如果返回值为0，先临时停止SQL Server Agent服务，执行以下语句启用后再启动Agent服务：

  ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
  

• 在MSDB库中创建专用的事件接收队列和绑定服务：

  USE msdb
  GO
  -- 创建接收事件的队列
  CREATE QUEUE JobFailureQueue
  GO
  -- 绑定队列到系统内置的事件通知服务约定
  CREATE SERVICE JobFailureService
  ON QUEUE JobFailureQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
  GO
  

• 创建服务器级别的事件通知，订阅所有作业失败的系统事件：

  CREATE EVENT NOTIFICATION TrackJobFailure
  ON SERVER
  FOR AUDIT_JOB_FAILURE
  TO SERVICE 'JobFailureService', 'current database'
  GO
  

• 创建队列激活的存储过程，事件到达时自动执行：
  存储过程中可以直接从事件消息的XML结构里解析出失败作业名称、失败步骤ID、错误信息、执行时长等所有需要的字段，之后直接调用你的工单创建逻辑即可：支持通过xp_cmdshell调用本地自定义exe/脚本、通过CLR存储过程调用外部接口、直接写SQL对接工单库等任意自定义逻辑。存储过程配置为队列自动激活后，事件到达就会立刻执行，触发延迟通常在1秒以内。

这个方案的优势：

• 零侵入：所有现有作业不需要做任何修改，不需要新增步骤、不需要调整失败跳转规则，全量配置脚本10分钟内即可部署完成
• 全覆盖：后续新增的任何SQL Server Agent作业都会自动纳入失败触发范围，不需要额外配置
• 高可靠：不会因为作业配置遗漏、步骤跳转逻辑写错导致漏触发，是实例层面的全局捕获

方案2：WMI事件订阅（备选，适合无法启用Service Broker的环境） #

如果你的环境有数据库侧的权限限制无法配置Service Broker，可以用WMI事件订阅实现同样的主动触发效果：

• 在SQL Server所在服务器部署一个轻量常驻脚本/服务，订阅SQL Server Agent命名空间下的作业失败WMI事件
• 事件触发时脚本自动解析作业失败信息，直接调用工单创建程序
• 这个方案同样是事件驱动，没有轮询开销，触发延迟在1秒级，也不需要修改任何现有作业配置
• 注意给运行脚本的账号授予对应WMI命名空间的读取权限、MSDB库的作业历史查看权限即可

避坑说明 #

不要用SQL Server Agent自带的「作业失败告警」功能实现，该功能仅支持发送邮件、网络消息等固定动作，无法自定义执行外部程序、对接工单系统的灵活逻辑，不满足需求。

你之前提出的定时轮询作业历史表的方案属于被动拉取，而上述两个方案都是系统在作业失败时主动推送事件触发逻辑，和你逐个给作业加工单步骤的触发时机完全一致，完全符合管理层对实时主动触发的要求。

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