Using alerts, you can send e-mail, pager, or Net Send alerts when errors occur or when performance conditions are reached. For example, you can configure an alert to send a message when a Log File Is Full error occurs or when the number of deadlocks per second is more than five. You can also execute a job on an alert event.
Default alerts are configured when you configure features such as replication. The names of alerts configured when you set up replication begin with Replication: and include the following:
Replication: Agent Success. An alert that tells you that the replication agent was successful.
Replication: Agent Failure. An alert that tells you that the replication agent failed.
Replication: Agent Retry. An alert that tells you that the replication agent failed and is retrying.
Replication: Expired Subscription Dropped. An alert that tells you that an expired subscription was dropped, which means the subscriber will not be updated anymore.
Replication: Subscriber Has Failed Data Validation. An alert that tells you that data in the subscriber’s subscription could not be validated.
Replication: Subscriber Has Passed Data Validation. An alert that tells you that data in the subscriber’s subscription was validated.
Replication: Subscriber Reinitialized After Validation Failure. An alert that tells you that data in the subscriber’s subscription was reinitialized with a new snapshot.
These replication alerts are disabled and do not have operators assigned either. So if you want to use these alerts, you will need to enable them and assign operators. Other default alerts for replication are used to issue warnings and are enabled in a standard configuration.
Error message alerts are triggered when SQL Server generates an error message. You can create an error message alert by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Expand the SQL Server Agent entry in the left pane by double-clicking it.
Right-click Alerts, and then select New Alerts from the shortcut menu. This displays the New Alert Properties dialog box, shown in Figure 15-4.
Type a short but descriptive name for the alert in the Name text box. In Figure 15-4, the alert is named Database Consistency Error.
In the Type selection list, choose SQL Server Event Alert. You can now set alerts according to the number or severity level of error messages.
Use the Database Name selection list to choose the database in which the error must occur in order to trigger the alert. To specify all databases on the server, select the <All Databases> option.
To set alerts by error number, choose Error Number, and then type an error number in the related text box. To see all error messages that can be returned by SQL Server, you query master using SELECT * FROM SYSMESSAGES as discussed in Chapter 13.
To set alerts by severity level, choose Severity, and then use the related selection list to choose a severity level that triggers the alert. You will usually want to configure alerts for severity levels 19 through 25 (which are the levels for fatal errors).
To restrict alerts to messages containing specific text strings, type the filter text in the Error Message Contains This Text box.
Configure the alert response as explained in the next subsection, "Handling Alert Responses." Click OK to create the alert.
In response to an alert, you can execute SQL Server jobs or notify operators of the alert, or both. To configure the alert response, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Expand the SQL Server Agent and Alerts folders.
Double-click the alert you want to configure. Then select the Response page, as shown in Figure 15-5.
To execute a job in response to the alert, select Execute Job.
If you want to execute an existing job, click the Properties (…) button to display the Locate Job dialog box. Enter the full or partial job name, and then click Check Names. If multiple possible matches are found, choose the job you want to run, and then click OK. Click OK to close the Locate Job dialog box. To be sure you have the right job, you can click View Job to view the job properties.
If you want to create a new job, click New Job, and then configure the job as discussed in the section titled "Scheduling Jobs" later in this chapter.
To notify designated operators of an alert rather than just logging the alert, select Notify Operators.
Operators configured to handle alerts and schedule jobs are shown in the Operators List area. The available notification methods depend on how the operator account is configured. You can select E-Mail, Pager, or Net Send notification, or all three. Click New Operator to configure a new operator or View Operator to view the properties of an operator currently selected in the Operator List.
Select the Options page.
Use the Include Alert Error Text In check boxes to specify if error text should be sent with the notification message. By default, error text is sent only with E-Mail and Net Send notifications.
Set an additional message to operators using the Additional Notification Message To Send text box.
Set the delay between responses for subsequent alert notifications using the Delay Between Responses boxes labeled Minutes and Seconds.
Click OK to complete the configuration.
Deleting an alert removes its entry from the alerts list. Because old alerts may be useful to you (or another database administrator) in the future, you may want to disable them instead of deleting them. When an alert is disabled, no alerts are triggered if the related event occurs.
To delete, enable, or disable an alert, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server running SQL Server Agent.
Expand the SQL Server Agent and Alerts folders.
Any alerts that are shaded are configured but not enabled. To enable or disable an alert, right-click it, and then select Enable or Disable as appropriate. Click Close.
To delete an alert, click it, and then press Delete. In the Delete Object dialog box, click OK to confirm the deletion.