sp_add_alert (Transact-SQL)sp_add_alert (Transact-SQL)

适用对象:yesSQL Server(从 2008 版开始)noAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建一个警报。Creates an alert.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


sp_add_alert [ @name = ] 'name'   
     [ , [ @message_id = ] message_id ]   
     [ , [ @severity = ] severity ]   
     [ , [ @enabled = ] enabled ]  
     [ , [ @delay_between_responses = ] delay_between_responses ]   
     [ , [ @notification_message = ] 'notification_message' ]   
     [ , [ @include_event_description_in = ] include_event_description_in ]   
     [ , [ @database_name = ] 'database' ]   
     [ , [ @event_description_keyword = ] 'event_description_keyword_pattern' ]   
     [ , { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ]   
     [ , [ @raise_snmp_trap = ] raise_snmp_trap ]   
     [ , [ @performance_condition = ] 'performance_condition' ]   
     [ , [ @category_name = ] 'category' ]   
     [ , [ @wmi_namespace = ] 'wmi_namespace' ]  
     [ , [ @wmi_query = ] 'wmi_query' ]  


[ @name = ] 'name' 警报的名称。[ @name = ] 'name' The name of the alert. 该名称显示在为响应警报而发送的电子邮件或寻呼消息中。The name appears in the e-mail or pager message sent in response to the alert. 它必须是唯一的并且可以包含百分比 (%) 字符。It must be unique and can contain the percent (%) character. 名称sysname,无默认值。name is sysname, with no default.

[ @message_id = ] message_id 定义警报消息错误号。[ @message_id = ] message_id The message error number that defines the alert. (它通常与中的错误号相对应sysmessages表。)message_idint,默认值为0(It usually corresponds to an error number in the sysmessages table.) message_id is int, with a default of 0. 如果严重性用来定义警报, message_id必须是0或为 NULL。If severity is used to define the alert, message_id must be 0 or NULL.


sysmessages错误写入 Microsoft Windows 应用程序日志可能会导致发送警报。Only sysmessages errors written to the Microsoft Windows application log can cause an alert to be sent.

[ @severity = ] severity 严重级别 (从1通过25),它定义警报。[ @severity = ] severity The severity level (from 1 through 25) that defines the alert. 任何SQL ServerSQL Server消息存储在sysmessages发送到的表MicrosoftMicrosoft使用指示的严重级别的 Windows 应用程序日志会导致发送警报。Any SQL ServerSQL Server message stored in the sysmessages table sent to the MicrosoftMicrosoft Windows application log with the indicated severity causes the alert to be sent. 严重性int,默认值为 0。severity is int, with a default of 0. 如果message_id用来定义警报,严重性必须是0If message_id is used to define the alert, severity must be 0.

[ @enabled = ] enabled 指示警报的当前状态。[ @enabled = ] enabled Indicates the current status of the alert. 已启用tinyint,默认值为 1 (启用)。enabled is tinyint, with a default of 1 (enabled). 如果0,警报未启用,并且不会激发。If 0, the alert is not enabled and does not fire.

[ @delay_between_responses = ] delay_between_responses 等待期 (以秒为单位,警报响应之间)。[ @delay_between_responses = ] delay_between_responses The wait period, in seconds, between responses to the alert. delay_between_responsesint,默认值为0,这意味着的响应 (警报的每个匹配项都生成响应) 之间不等待。delay_between_responsesis int, with a default of 0, which means there is no waiting between responses (each occurrence of the alert generates a response). 响应可以为下面的一种或两种形式:The response can be in either or both of these forms:

  • 通过电子邮件或寻呼发送的一个或多个通知。One or more notifications sent through e-mail or pager.

  • 要执行的作业。A job to execute.

例如,当警报在短时间内重复产生时,通过设置该值就有可能避免发送重复的电子邮件。By setting this value, it is possible to prevent, for example, unwanted e-mail messages from being sent when an alert repeatedly occurs in a short period of time.

[ @notification_message = ] 'notification_message' 发送给操作员的电子邮件的一部分的可选附加消息网络发送,或寻呼通知。[ @notification_message = ] 'notification_message' Is an optional additional message sent to the operator as part of the e-mail, net send, or pager notification. notification_messagenvarchar(512),默认值为 NULL。notification_message is nvarchar(512), with a default of NULL. 指定notification_message对于添加特殊说明如补救过程很有用。Specifying notification_message is useful for adding special notes such as remedial procedures.

[ @include_event_description_in = ] include_event_description_in 是是否的说明SQL ServerSQL Server错误应包含在通知消息。[ @include_event_description_in = ] include_event_description_in Is whether the description of the SQL ServerSQL Server error should be included as part of the notification message. include_event_description_intinyint,默认值为5 (电子邮件和网络发送),并可以具有一个或多个值与组合逻辑运算符。include_event_description_inis tinyint, with a default of 5 (e-mail and net send), and can have one or more of these values combined with an OR logical operator.


的未来版本中,将从 代理中删除寻呼程序和 SQL ServerSQL Server net send MicrosoftMicrosoftSQL ServerSQL Server选项。The Pager and net send options will be removed from SQL ServerSQL Server Agent in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用这些功能,并考虑修改当前使用这些功能的应用程序。Avoid using these features in new development work, and plan to modify applications that currently use these features.

ReplTest1Value DescriptionDescription
00 NoneNone
11 电子邮件E-mail
22 寻呼程序Pager
44 net sendnet send

[ @database_name = ] 'database' 在其中出现错误时必须触发该警报数据库。[ @database_name = ] 'database' The database in which the error must occur for the alert to fire. 如果数据库未提供,而不考虑发生错误的位置触发警报。If databaseis not supplied, the alert fires regardless of where the error occurred. 数据库sysnamedatabase is sysname. 不允许用方括号 ([ ]) 将名称括起来。Names that are enclosed in brackets ([ ]) are not allowed. 默认值为 NULL。The default value is NULL.

[ @event_description_keyword = ] 'event_description_keyword_pattern' 一系列字符的说明SQL ServerSQL Server错误必须与之类似。[ @event_description_keyword = ] 'event_description_keyword_pattern' The sequence of characters that the description of the SQL ServerSQL Server error must be like. 可以使用 Transact-SQLTransact-SQL LIKE 表达式模式匹配字符。Transact-SQLTransact-SQL LIKE expression pattern-matching characters can be used. event_description_keyword_patternnvarchar(100),默认值为 NULL。event_description_keyword_pattern is nvarchar(100), with a default of NULL. 此参数可用于筛选对象名称 (例如, %customer_table%)。This parameter is useful for filtering object names (for example, %customer_table%).

[ @job_id = ] job_id 作业以响应该警报而运行的作业标识号。[ @job_id = ] job_id The job identification number of the job to run in response to this alert. job_iduniqueidentifier,默认值为 NULL。job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name' 为了响应该警报而执行作业的名称。[ @job_name = ] 'job_name' The name of the job to be executed in response to this alert. job_namesysname,默认值为 NULL。job_nameis sysname, with a default of NULL.


任一job_idjob_name必须指定,但不能同时指定两者。Either job_id or job_name must be specified, but both cannot be specified.

[ @raise_snmp_trap = ] raise_snmp_trap 在中未实现SQL ServerSQL Server版本 7.0。[ @raise_snmp_trap = ] raise_snmp_trap Not implemented in SQL ServerSQL Server version 7.0. raise_snmp_traptinyint,默认值为 0。raise_snmp_trap is tinyint, with a default of 0.

[ @performance_condition = ] 'performance_condition' 格式表示的值itemcomparatorvalue[ @performance_condition = ] 'performance_condition' Is a value expressed in the format 'itemcomparatorvalue'. performance_conditionnvarchar(512) 默认值为 NULL,并包含这些元素。performance_condition is nvarchar(512) with a default of NULL, and consists of these elements.

格式元素Format element DescriptionDescription
Item 性能对象、性能计数器或计数器的命名实例A performance object, performance counter, or named instance of the counter
ComparatorComparator 下列运算符之一:>、< 或 =One of these operators: >, <, or =
ReplTest1Value 计数器的数值Numeric value of the counter

[ @category_name = ] 'category' 警报类别的名称。[ @category_name = ] 'category' The name of the alert category. 类别sysname,默认值为 NULL。category is sysname, with a default of NULL.

[ @wmi_namespace = ] 'wmi_namespace' 事件的查询的 WMI 命名空间。[ @wmi_namespace = ] 'wmi_namespace' The WMI namespace to query for events. wmi_namespacesysname,默认值为 NULL。wmi_namespace is sysname, with a default of NULL. 只支持本地服务器上的命名空间。Only namespaces on the local server are supported.

[ @wmi_query = ] 'wmi_query' 指定警报的 WMI 事件查询。[ @wmi_query = ] 'wmi_query' The query that specifies the WMI event for the alert. wmi_querynvarchar(512),默认值为 NULL。wmi_query is nvarchar(512), with a default of NULL.

返回代码值Return Code Values

0 (成功) 或1 (失败)0 (success) or 1 (failure)

结果集Result Sets



sp_add_alert必须从运行msdb数据库。sp_add_alert must be run from the msdb database.

在下列情况下,SQL ServerSQL ServerSQL ServerSQL Server 应用程序产生的错误/消息将发送到 Windows 应用程序日志,并因此引发警报:These are the circumstances under which errors/messages generated by SQL ServerSQL Server and SQL ServerSQL Server applications are sent to the Windows application log and can therefore raise alerts:

  • 严重级别为 19 或更高版本sys.messages错误Severity 19 or higher sys.messages errors

  • 任何使用 WITH LOG 语法调用的 RAISERROR 语句Any RAISERROR statement invoked with WITH LOG syntax

  • 任何sys.messages错误修改或创建使用sp_altermessageAny sys.messages error modified or created using sp_altermessage

  • 记录使用的任何事件xp_logeventAny event logged using xp_logevent

SQL Server Management StudioSQL Server Management Studio 提供了一种易用的图形方式来管理整个警报系统,这也是配置警报基础结构的推荐方式。provides an easy, graphical way to manage the entire alerting system and is the recommended way to configure an alert infrastructure.

如果一个警报没有正常工作,请检查:If an alert is not functioning properly, check whether:

  • SQL ServerSQL Server Agent 服务是否正在运行。The SQL ServerSQL Server Agent service is running.

  • 事件是否出现在 Windows 应用程序日志中。The event appeared in the Windows application log.

  • 警报是否已被启用。The alert is enabled.

  • xp_logevent 生成的事件在 master 数据库中发生。Events generated with xp_logevent occur in the master database. 因此,除非警报的 xp_logevent 为 **@database_name** 或 NULL,否则 **@database_name** 不触发警报。Therefore, xp_logevent does not trigger an alert unless the **@database_name** for the alert is 'master' or NULL.


默认情况下,只有 sysadmin 固定服务器角色的成员才能执行 sp_add_alertBy default, only members of the sysadmin fixed server role can execute sp_add_alert.


以下示例在触发警报时添加一个运行 Back up the AdventureWorks2012 Database 作业的警报(测试警报)。The following example adds an alert (Test Alert) that runs the Back up the AdventureWorks2012 Database job when fired.


此示例假定消息 55001 和 Back up the AdventureWorks2012 Database 作业已存在。This example assumes that the message 55001 and the Back up the AdventureWorks2012 Database job already exist. 此示例只用于说明用途。The example is shown for illustrative purposes only.

USE msdb ;  
EXEC dbo.sp_add_alert  
    @name = N'Test Alert',  
    @message_id = 55001,   
   @severity = 0,   
   @notification_message = N'Error 55001 has occurred. The database will be backed up...',   
   @job_name = N'Back up the AdventureWorks2012 Database' ;  

请参阅See Also

sp_add_notification (Transact-SQL) sp_add_notification (Transact-SQL)
sp_altermessage (Transact-SQL) sp_altermessage (Transact-SQL)
sp_delete_alert (Transact-SQL) sp_delete_alert (Transact-SQL)
sp_help_alert (Transact-SQL) sp_help_alert (Transact-SQL)
sp_update_alert (Transact-SQL) sp_update_alert (Transact-SQL)
sys.sysperfinfo (Transact-SQL) sys.sysperfinfo (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)