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, 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.

Figure 5-10: Creating an 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 <New Job>, 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.