TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_help_alert (Transact-SQL)

 

Updated: August 9, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Reports information about the alerts defined for the server.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_help_alert [ [ @alert_name = ] 'alert_name' ]   
     [ , [ @order_by = ] 'order_by' ]   
     [ , [ @alert_id = ] alert_id ]   
     [ , [ @category_name = ] 'category' ]   
     [ , [ @legacy_format = ] legacy_format ]  

[ @alert_name =] 'alert_name'
The alert name. alert_name is nvarchar(128). If alert_name is not specified, information about all alerts is returned.

[ @order_by =] 'order_by'
The sorting order to use for producing the results. order_byis sysname, with a default of N 'name'.

[ @alert_id =] alert_id
The identification number of the alert to report information about. alert_idis int, with a default of NULL.

[ @category_name =] 'category'
The category for the alert. category is sysname, with a default of NULL.

[ @legacy_format=] legacy_format
Is whether to produce a legacy result set. legacy_format is bit, with a default of 0. When legacy_format is 1, sp_help_alert returns the result set returned by sp_help_alert in Microsoft SQL Server 2000.

0 (success) or 1 (failure)

When @legacy_format is 0, sp_help_alert produces the following result set.

Column nameData typeDescription
idintSystem-assigned unique integer identifier.
namesysnameAlert name (for example, Demo: Full msdb log).
event_sourcenvarchar(100)Source of the event. It will always be MSSQLServer for Microsoft SQL Server version 7.0
event_category_idintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
event_idintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
message_idintMessage error number that defines the alert. (Usually corresponds to an error number in the sysmessages table). If severity is used to define the alert, message_id is 0 or NULL.
severityintSeverity level (from 9 through 25, 110, 120, 130, or 140) that defines the alert.
enabledtinyintStatus of whether the alert is currently enabled (1) or not (0). A nonenabled alert is not sent.
delay_between_responsesintWait period, in seconds, between responses to the alert.
last_occurrence_dateintData the alert last occurred.
last_occurrence_timeintTime the alert last occurred.
last_response_dateintDate the alert was last responded to by the SQLServerAgent service.
last_response_timeintTime the alert was last responded to by the SQLServerAgent service.
notification_messagenvarchar(512)Optional additional message sent to the operator as part of the e-mail or pager notification.
include_event_descriptiontinyintIs whether the description of the SQL Server error from the Microsoft Windows application log should be included as part of the notification message.
database_namesysnameDatabase in which the error must occur for the alert to fire. If the database name is NULL, the alert fires regardless of where the error occurred.
event_description_keywordnvarchar(100)Description of the SQL Server error in the Windows application log that must be like the supplied sequence of characters.
occurrence_countintNumber of times the alert occurred.
count_reset_dateintDate the occurrence_count was last reset.
count_reset_timeintTime the occurrence_count was last reset.
job_iduniqueidentifierIdentification number of the job to be executed in response to an alert.
job_namesysnameName of the job to be executed in response to an alert.
has_notificationintNonzero if one or more operators are notified for this alert. The value is one or more of the following values (ORed together):

 1=has e-mail notification

 2=has pager notification

 4=has net send notification.
flagsintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
performance_conditionnvarchar(512)If type is 2, this column shows the definition of the performance condition; otherwise, the column is NULL.
category_namesysnameIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed. Will always be '[Uncategorized]' for SQL Server 7.0.
wmi_namespacesysnameIf type is 3, this column shows the namespace for the WMI event.
wmi_querynvarchar(512)If type is 3, this column shows the query for the WMI event.
typeintType of the event:

 1 = SQL Server event alert

 2 = SQL Server performance alert

 3 = WMI event alert

When @legacy_format is 1, sp_help_alert produces the following result set.

Column nameData typeDescription
idintSystem-assigned unique integer identifier.
namesysnameAlert name (for example, Demo: Full msdb log).
event_sourcenvarchar(100)Source of the event. It will always be MSSQLServer for SQL Server version 7.0
event_category_idintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
event_idintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
message_idintMessage error number that defines the alert. (Usually corresponds to an error number in the sysmessages table). If severity is used to define the alert, message_id is 0 or NULL.
severityintSeverity level (from 9 through 25, 110, 120, 130, or 140) that defines the alert.
enabledtinyintStatus of whether the alert is currently enabled (1) or not (0). A nonenabled alert is not sent.
delay_between_responsesintWait period, in seconds, between responses to the alert.
last_occurrence_dateintData the alert last occurred.
last_occurrence_timeintTime the alert last occurred.
last_response_dateintDate the alert was last responded to by the SQLServerAgent service.
last_response_timeintTime the alert was last responded to by the SQLServerAgent service.
notification_messagenvarchar(512)Optional additional message sent to the operator as part of the e-mail or pager notification.
include_event_descriptiontinyintIs whether the description of the SQL Server error from the Windows application log should be included as part of the notification message.
database_namesysnameDatabase in which the error must occur for the alert to fire. If the database name is NULL, the alert fires regardless of where the error occurred.
event_description_keywordnvarchar(100)Description of the SQL Server error in the Windows application log that must be like the supplied sequence of characters.
occurrence_countintNumber of times the alert occurred.
count_reset_dateintDate the occurrence_count was last reset.
count_reset_timeintTime the occurrence_count was last reset.
job_iduniqueidentifierJob identification number.
job_namesysnameAn on-demand job to be executed in response to an alert.
has_notificationintNonzero if one or more operators are notified for this alert. The value is one or more of the following values (joined together with OR):

 1=has e-mail notification

 2=has pager notification

 4=has net send notification.
flagsintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed..
performance_conditionnvarchar(512)If type is 2, this column shows the definition of the performance condition. If type is 3, this column shows the query for the WMI event. Otherwise, the column is NULL.
category_namesysnameIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed. Will always be '[Uncategorized]' for SQL Server 7.0.
typeintType of alert:

 1 = SQL Server event alert

 2 = SQL Server performance alert

 3 = WMI event alert

sp_help_alert must be run from the msdb database.

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted the SQLAgentOperatorRole fixed database role in the msdb database.

For details about SQLAgentOperatorRole, see SQL Server Agent Fixed Database Roles.

The following example reports information about the Demo: Sev. 25 Errors alert.

USE msdb ;  
GO  
  
EXEC sp_help_alert @alert_name = 'Demo: Sev. 25 Errors';  
GO  

sp_add_alert (Transact-SQL)
sp_update_alert (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft