To create an alert using an error numberErrors and messages, or events, are generated by Microsoft® SQL Server™ and entered into the Microsoft Windows NT® 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 NT application log:
You can use sp_altermessage to designate specific sysmessages errors as “always logged” to log error messages with a severity lower than 19.
RAISERROR WITH LOG is the recommended way to write to the Windows NT application log from SQL Server.
Note Make sure that the Windows NT 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.
Every alert must have a name. Alert names must be unique and can be no longer than 128 characters.
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:
SQL Server Agent fires an alert when a specific error occurs.
SQL Server Agent fires an alert when any error of the specific severity occurs.
Specifies a database in which the event occurred if you want to restrict the alert.
Specifies a text string in the event message if you want to restrict the alert.
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:
The area of SQL Server performance to be monitored.
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.
The specific instance (if any) of the attribute to be monitored.
The behavior the counter or counter instance must exhibit for the alert to fire.
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 50000. Additionally, you can assign them a severity level.
User-defined event messages must be unique and have a unique error number and 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 NT application log when they occur and therefore do not trigger SQL Server Agent alerts.
If you administer a multiple language SQL Server environment, you should 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
To create an alert using severity level
To define the response to an alert
To create a user-defined event error message
To edit a user-defined event error message
To delete a user-defined event error message
To disable or reactivate an alert