SQL Server Agent – Creating Alert Definitions

This article is the second in my new series that focuses on alerts and errors within the SQL Server Agent subsystem. In this article I am going to describe how to create new alert definitions. First, let’s recap the definition and pointers for the use of SQL Server Agent Alerts from my previous article:

• 
  Alert – A definition that matches one or more SQL Server events and a response, should those events occur. The DBA can control the response to events that have occurred with alerts (but not the event’s occurrence itself, obviously). The use of alerts allows for automatic database server administration.
  
• 
  Every Microsoft SQL Server 7.0 installation has a pre-defined set of 11
  demo alerts templates.
  
• 
  DBAs can modify and adjust the existing alerts templates to meet their needs.
  
• 
  DBAs can also create their own, custom alerts.

Creating New Alerts

To create new alerts, you need to complete following steps:

1. Start SQL Server administrative console – Enterprise Manager (typically:
   Start -> Programs -> Microsoft SQL Server 7.0 -> Enterprise Manager).
2. Make a connection to a database server and drill-down SQL Server
   objects to get to Alerts (your_database_server_name -> Management ->
   SQL Server Agent -> Alerts). After placing a mouse cursor on it in the right
   (details) pane of Enterprise Manager console, you will see an existing set of
   pre-defined alerts templates.
3. Right mouse button click on the details pane and select “New Alert . . .” in the context-dependent menu.
4. In the “New Alert Properties – (your_database_server_name)” dialog window,
   define the following options for the newly created alert definition:

   On the “General” bookmark, the following alert options should be defined:

   • Name – A “human understandable” name for the newly created alert.
   • Type – Define whether the new alert will fire upon a specific event that occurs
     (either error number or severity number) or upon a specific performance condition
     (when a specified performance threshold is reached).

When selecting the alert type SQL Server event alert, the following
additional paramaters should also be defined:

• 
  Error number – The alert will fire when a specific error occurs; or Severity
  – The alert fires when an error with a specific severity occurs.
• 
  Database name – Specifies a database in which the event occurs (restricts the alert to a specific database).
• 
  Error message contains the text – Specifies a text string the event
  message should contain (if you want to restrict the alert).

When setting an alert type for a SQL Server performance condition alert,
the following options should be defined:

• 
  Object – The SQL Server performance object to be monitored (for example, SQL Server: Locks)
• 
  Counter – The SQL Server performance object attribute to be monitored
  (for example, for the above object – Number of Deadlocks/sec)
• 
  Instance – The specific instance (if any) of the attribute to be
  monitored (for example, for the above object and counter you can select Database)
• 
  Alert if counter/Value – The behavior the counter or counter instance
  must exhibit in order for the alert to fire (in our case, it should probably be Rises
  above / 1).

On the “Response” bookmark, the DBA will likely want to define the following additional alert options:

• 
  Execute job – The name of the SQL Server Agent job which will be executed automatically on alert fire (if any)
• 
  Operators to notify – Operators to notify and the method of their notification (options include e-mail, paging, net send or a combination of the above);
• 
  Delay between responses – The delay (in minutes and seconds) between responses for a recurring alert.

See All Articles by Columnist Alexzander Nepomnjashiy