How to Create an Alert Using SQL Server Agent

SQL Server Agent allows automatic notification of database administrators when unusual circumstances occur. This powerful alert mechanism enables 24-hour monitoring of the database performance without staffing a 24-hour operations center.

General Requirement for Defining an Alert

To define an alert, you need specific basic information including:

• Alert Name: Alert names must be unique in SQL Server. They can be no longer than 128 characters.
• Event: The event that triggers the alert - The event type determines the parameters used. The three types of alerts are SQL Server events, SQL Server performance conditions and Windows Management Instrumentation events.
• Action: The action that SQL Server Agent takes when the event is triggered. Any alert can be assigned either (or both) of these two alert types: Execute a SQL Server Agent job and/or Notify an operator.

Step-By-Step SQL Server Alert Setup

These instructions apply to SQL Server 2005 and newer:

1. Open SQL Server Management Studio and connect to the database server where you want to create an alert.
2. Expand the SQL Server Agent folder by clicking once on the "+" icon to the left of the folder.
3. Right-click on the Alerts folder and select New Alert from the pop-up menu.
4. Type a descriptive name for your alert in the Name text box.
5. Choose the type of alert from the drop-down menu. Your choices are SQL Server performance conditions such as CPU load and free disk space, SQL Server events such as fatal errors, syntax errors and hardware issues, and Windows Management Instrumentation (WMI) events.
6. Provide any alert-specific details requested by SQL Server such as specific text included in the event report and parameters for performance condition alerts.
7. Click the Response icon in the New Alert window's Select a page pane.
8. If you want to execute a SQL Server Agent job when the alert occurs, click the Execute job checkbox and select a job from the drop-down menu.
9. If you want to notify database operators when the alert occurs, click the Notify operators checkbox and then select the operators and notification types from the grid.
10. Click OK to create the alert.

Adding Alerts Using Transact-SQL

Beginning with SQL Server 2008, you can also add alerts using Transact-SQL. Use this syntax from Microsoft:

sp_add_alert [ @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' ]