Alerts

Alerts constitute the "reactive" component of Scheduling and Notification. The SQL Server Agent monitors the Windows Application Log; when an event occurs, the Agent compares that event to its list of alerts. If an alert has been defined on the event, the action defined for that alert is fired . Alerts can be configured to fire on SQL Server or user -defined error messages, or based on performance condition thresholds. When defining alerts to respond to error messages, remember that the error must be set to write to the application log or the alert won't fire. Only severity 19 and higher errors are required to write to the log, but all others can be configured to do so. Errors can be managed from the Enterprise Manager Tools menu, or through the Properties dialog box for the alert.

NOTE

If you are running SQL Server on Windows 98, no Application log is available to which to send the error messages. In this case, the Event Viewer must be substituted by a SQL Server Profiler trace.

Defining Alert Properties

As with jobs and operators, alerts are managed from the SQL Server Agent folder in Enterprise Manager. Right-click the folder and select New Alert to access the Alert Properties dialog box. From the General tab, you can name the alert, select the type of alert, and select whether the alert is to be enabled. If you select the type to be SQL Server Event Alert, you can enter a specific error number or choose a severity level. If you don't know the specific error number, clicking the Ellipse button ( ) gives you access to a search tool. The Database drop-down box allows you to pick a particular database or to define the alert on all databases. There is also the option to enter a text string that the error must contain for the alert to fire. Configuring an alert for a SQL Server error is shown in Figure 18.12.

Figure 18.12. Configuring an alert for a SQL Server error.

If you choose to create the alert as a type SQL Server Performance Condition Alert, you are presented with a different set of options. Performance condition alerts use the same counters that are used to monitor SQL Server with the Windows Performance Monitor. Select the object to monitor, the specific counter for the object, the instance (database) you want to monitor, and the value for the counter that will fire the alert. The alert can be set to fire if the counter rises above, falls below, or is equal to the value entered. Figure 18.13 shows a performance condition alert that fires when the transaction log of the Pubs database is more than 80% full.

Figure 18.13. Configuring an alert for a performance condition.

Defining Alert Responses and Operators

The Response tab enables you to define what to do when the alert fires. You can choose to run a job, as well as define operators to be notified. Notification of an alert is helpful, but the real power of the alert system lies in its ability to run a job. For example, the alert defined in Figure 18.13 fires when the transaction log reaches 80% full. This alert can be defined to run a job that backs up the log. As you saw earlier, the job can contain multiple steps to ensure it completes or notify someone if it doesn't. This allows automated monitoring and correction of potential problems before they reach the critical stage. Figure 18.14 shows the Response tab for just such an alert.

Figure 18.14. Configuring an alert response.