The following excerpt, courtesy of McGraw-Hill Osborne Media, is from
Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.
Alerts
Alerts give SQL Server the ability to trap certain events and send pages, e-mails,
or popup messages with the error. Alerts also allow you to execute a job when an
event is trapped. An alert can enable you to trap certain performance points that are
common to System Monitor. SQL Server ships with a number of sample alerts that
only need responses to complete them.
Creating an Alert
To create an alert, ensure that SQL Server Agent is running. In Enterprise Manager,
go to the Alert group under SQL Server Agent. Right-click Agent and select New
Alert from the menu.
In the General tab (see Figure 5-10), name your alert and specify what error, or
type of error, will trigger an event. To choose an individual error number to trap,
Figure 5-10 Creating an alert
select the Error Number option. To choose a particular level of error, select the
Severity Level option. Use the Type drop-down box to select either a performance
event or a SQL Server event alert.
For errors to trigger events, the errors must be logged. Enterprise Manager
prompts you if the error is not logged yet, or you can go to the Manage Messages
screen under the Tools menu to enable logging.
The events can trigger responses that can be configured in the Response tab. The
Response tab tells SQL Server Agent what to do when the error or performance
condition is trapped. A response can do the following:
Execute a job
Page, e-mail, or send a popup message to an operator or multiple operators
You can use both response types, executing a job to correct the error, then sending
a message. For example, if you trap an error when the transaction log is full, you can
perform a transaction log dump. First, execute a job in the Response tab by checking
the Execute Job option. Select the job from the drop-down box, create a new job by
selecting , or modify an old job by clicking "…".
Then select the operators to whom you want to send messages, and indicate the
method for sending the message. You can select multiple operators, and whoever is
on duty receives the message. If you wish, you can include custom messages in the
notification.
The Delay Between Responses option tells SQL Server how soon to send another
notification after another error is trapped. For example, if you had set this option for
10 minutes and you were notified of an error, SQL Server would not notify you
again until 10 minutes passed even if another error occurs. This is nice, because
errors usually are followed by a string of similar errors as each connection triggers
the error again. Rather than receive 100 pages before you are able to get to the
server to remedy the problem, set this option to the amount of time it takes you to
fix the problem.
Setting Performance Alerts
You can also set alerts that trigger messages or jobs (or both) when certain performance
indicators reach a given point. For example, if the memory on your SQL Server falls
below the acceptable level, you could have yourself paged or run a job to free up
resources. You can set the triggering point to be where the counter falls below,
rises above, or is equal to any positive number.
Click for the next excerpt in this series: Setting Job Notifications
Click for the
complete book excerpt series.
