Defining Alerts

Errors and messages, or events, are generated by Microsoft® SQL Server™ and entered into the Microsoft Windows® application log. SQL Server Agent reads the application log and compares events to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert.

By default, the following SQL Server events are logged in the Windows application log:

• Severity 19 or higher sysmessages errors.

  You can use sp_altermessage to designate specific sysmessages errors as "always logged" to log error messages with a severity lower than 19.

• Any RAISERROR statement invoked by using the WITH LOG syntax.

  RAISERROR WITH LOG is the recommended way to write to the Windows application log from an instance of SQL Server.

• Any application logged by using xp_logevent.

  Note  Make sure that the Windows application log is of sufficient size to avoid losing SQL Server event information.

Alerts must be defined before notifications can be sent. The primary attributes of an alert are name and event or performance condition specification.

Naming an Alert

Every alert must have a name. Alert names must be unique and can be no longer than 128 characters.

Selecting an Event

You can specify an alert to occur in response to one or more events. You specify the set of events to trigger an alert according to:

• Error number.

  SQL Server Agent fires an alert when a specific error occurs.

• Severity level.

  SQL Server Agent fires an alert when any error of the specific severity occurs.

• Database.

  Specifies a database in which the event occurred if you want to restrict the alert.

• Event text.

  Specifies a text string in the event message if you want to restrict the alert.

Selecting a Performance Condition

You can specify a performance condition to monitor by firing an alert when the performance threshold is reached. To set a performance condition you must define the following:

• Object.

  The area of SQL Server performance to be monitored.

• Counter.

  The attribute with the area to be monitored. Performance data is sampled periodically, which can lead to a small delay (a few seconds) between the threshold being reached and the performance alert firing.

• Instance.

  The specific instance (if any) of the attribute to be monitored.

• Alert if counter/value.

  The behavior the counter or counter instance must exhibit for the alert to fire.

Creating a User-defined Event Message

You can create user-defined event messages if you have special event tracking needs that are not addressed by standard SQL Server event messages. User-defined event messages generate error numbers greater than 50,000. Additionally, you can assign them a severity level.

User-defined event messages must be unique and have a unique error number. They can each have a unique language.

Note  When using SQL Server Enterprise Manager, you should select the Write to Windows NT application event log option. By default, user-defined messages with severities less than 19 are not sent to the Windows application log when they occur and therefore do not trigger SQL Server Agent alerts.

If you administer a multiple language SQL Server environment, create user-defined messages in each of the languages you support. For example, if you are creating a new event message that will be used on both an English and a German server, use the same event number for both, but assign a different language for each.

To create an alert using an error number