Automatic alerts can be a lifesaver for database administrators. In yesterday's article, I discussed the methods you can use to create SQL Server operators. If you missed that article, you can read it here. Today, I’ll examine how you can configure alerts.
Once you create an operator, you must configure alerts. You can do this by using Enterprise Manager or use Transact-SQL by calling the sp_add_alert stored procedure. Here’s how to configure alerts using Enterprise Manager.
First, click to expand Management | SQL Server Agent | Alerts. Next, right-click on Alerts and choose New from the pop-up menu. In the Name field in the General tab, enter the name of the alert (which, again, is limited to 128 characters). For example, you could type Out of Space Alert. ID: New will indicate that a new alert is being created.
In the Type combo box, you can either select SQL Server Event Alert to fire the alert when particular events occur on a SQL Server, or you can choose SQL Server Performance Condition Alert to fire a notification when a performance threshold is reached.
Configuring alerts based on events
Several options will appear when you configure alerts based on events. You should click Error Number if you want to notify the operator about a particular error condition in SQL Server. If you are not sure about the error number, click on the browse button (…) and select the particular error you want to trap. This option will fire the alert only if that specific error occurs.
You can also create user-defined error messages for special events. These events can be associated either with error numbers greater than 50,000 or with a severity level. To specify a severity level, select the Severity option and pick a severity from the list. Note that a severity is not triggered by a single event. For example, a severity number “015 Syntax Error in SQL Statements” will be fired on all kinds of syntax errors in SQL statements. A severity may not necessarily be an error condition.
If you want to associate the alert condition with a specific database, select a Database name. Type in a text pattern in Error Message Contains This Text if you want to restrict the alert in addition to error number or severity.
Configuring alerts based on performance conditions
If you configure an alert by selecting the SQL Server Performance Condition Alert option, the following appear:
- Object: Select an object of SQL Server performance to monitor (e.g. Memory Manager).
- Counter: Select a specific parameter for your chosen object (e.g. Total Server Memory).
- Instance: Specify an instance associated with your object.
- Alert If Counter: Enter a threshold value for the counter.
- Value: Specify the value for the Alert If Counter.
- Enable: Select the check box to make the alert active.
The History frame shows the history of this particular alert over time. You can use the Reset Count button to reset the total occurrence of this event back to zero.
Now that you've set the alert condition, the next step is to specify a response using one of the methods we discussed yesterday:
- Execute a job.
- Send an e-mail message.
- Send notification to a pager.
- Notify a networked user.
You can set the method in the Response tab. The first option in the Response tab is Execute Job. A job can perform a series of instructions to automate common administrative tasks, such as backup, import/export, publishing, and so on. You can create a job using Enterprise Manager (or select an existing job from the combo box, if you've already created one).
If you want to use one of the other notification methods, you must first specify an operator to receive the alert. The Operators To Notify list will show you all the operators created on the server. (If you have not created an operator or you want to create a new one, use the New Operator button to do so. Follow the steps outlined in yesterday's article to create the operator.) Select as many operators from the list as you want by specifying the mechanism of notification.
Select the appropriate check boxes to notify by e-mail, pager, or Net send. Then, select the desired check boxes in Include Alert Text In if you want to include the full error message along with notification.
Apart from error text, you can specify your own message in the Additional Notification Message To Send To operator box. You can also specify time intervals between first and subsequent notifications. This is particularly useful if an alert is set on a condition that occurs often.
Configuring your server for e-mail
The last step is to configure the SQL Mail component for notification via e-mail or pager. You can do this either by using a Microsoft Outlook client with Microsoft Exchange, Microsoft Mail using NT Post Office (without Exchange Server), or a Microsoft Outlook/Exchange client and an SMTP Server.
Here, I’ll describe the configuration using the first method (having an Outlook client and Exchange server). The configuration consists of three steps:
- Create an e-mail account.
- Create a profile on the SQL Server.
- Test the e-mail account.
Create an e-mail account
Step one is to create an e-mail account on the Exchange Server. Assign permissions for the new account to the Windows NT account used by the default SQL Server service. If the service is using a system account, you must change it to an NT user account. You can find the current SQL Server service account in the Server Properties Under Security tab in Enterprise Manager.
Create a profile on the SQL Server
Log on to the SQL Server using the above-mentioned service account. Double-click on the Mail icon in Control Panel and click Add (assuming Microsoft Outlook is installed). Select the Microsoft Exchange Server check box and click Next. Then, type the Exchange Server name. The Mailbox field should automatically be filled with the service account. Select No when asked for traveling. Select the Personal Address Book path or leave it as the default. It is recommended to have this path as: C:\Winnt\Profiles\ServiceAccount. In the next screen, click No in response to the prompt to put Outlook in the Startup group. Click Finish to complete the process.
Test the e-mail account
In the Enterprise Manager, expand the Server tree to display Supported Services. Right-click on SQL Mail and select Properties. In the Profile Name field, select the profile you created above. Click the Autostart SQL Mail When SQL Server Starts check box and then click OK. Now start Query Analyzer from the Tools menu and use a stored procedure to send test mails.
To send a simple test mail, type:
xp_sendmail @recipients=‘ServiceAccount’ , @subject=’A Test Mail’, @message=’This is a test mail….’
To send an attachment with mail and a CC copy, type:
xp_sendmail @recipients=‘ServiceAccount’ , @copy_recipient=’AnotherAccount’, @subject=’A Test Mail with CC example’, @message=’This mail has an attachment’, @attachments=’c:\temp\readme.txt’
To send query results as an attachment, type:
xp_sendmail @recipients=‘ServiceAccount’ , @subject=’Query Results’, @message=’This mail has an attachment of query results’, @query=’Select * From employee’, @dbuse=’pubs’, @attached_results=’TRUE’.
If you'd like to share your opinion, please post a comment below or send the editor an e-mail.