Lab Files: Alerts and Operators

This set of lab exercises supplements the Certifiably SQL "Alerts and Operators" column in the May 2000 issue of SQL Server Magazine. Because the column hasn't covered the intricacies of SQL Mail yet, these exercises use the Net Send command to send alerts. However, if you already have SQL Mail implemented in your organization, you can also try sending email notifications.

Related: Alerts and Operators

Defining an Operator

Open SQL Server Enterprise Manager and expand the hierarchy so that you see the server you're connecting to. Then, expand the hierarchy so that you see Management, SQL Server Agent, and finally the Alerts, Operators, and Jobs items. Select Operators, then in the right pane, right-click and select New Operator from the pop up menu. (You can also right-click the Operators item in the hierarchy in the left pane).

Type in a name for the operator. You can use your own name or something like SQLDBA. Then enter the Net Send address, which should be either a computer name or the username for the person you want to notify of the alert. (If you've configured a pager address, make sure you've correctly set the operator's working hours.)

Click the Notifications tab, and make sure you've checked the "Operator is available to receive notifications" box. Click OK to close the New Operator Properties dialog box. You have just defined a new operator and are now ready to configure alerts for the operator.

Defining an Alert

To define an alert, first right-click Alerts or click Alerts then right-click the open area in the right pane that lists the Demo alerts. Select New Alert to open the New Alert Properties dialog box. Name the alert. For this example, use something like Backup Failure Alert. This will be a SQL Server event alert.

Choose the "Error number" option for defining the alert, then click the ellipsis to the right of the "Error number" box. Type the word "backup" in the "Message text contains" box, then click Find to bring up the Manage SQL Server Messages dialog box.

Highlight message 3013, and note that it's a severity level 16 message. Click Edit, and note that this screen shows 3013's severity level as 15 (don't ask me why; probably some programmers start counting from zero and others started from one). Click Cancel, then New to open the New SQL Server Message dialog box, which lets you define error messages for use in your own code. The error number defaults to 50001 if you haven't added any messages, which suggests that Microsoft has reserved 1 through 50,000 for SQL Server error messages. Click Cancel. With error 3013 still highlighted, click OK to select that error message.

Leave All Databases selected, unless you have some databases you don't want to monitor.

Click the Response tab. In this example, you haven't defined a job to run when this alert activates, but you do want to notify the operator, so check the box under Net Send (and if appropriate, for email and pager) for the operator you created. Then, check the box to include the error message text in the alert message. To add more information to the message, you can type in something like "Database backup failure. Immediate response required."

Change the delay for how long the system waits before resending the message to 3 minutes, unless you want to see the message several times. Then Click OK to close the New Alert Properties dialog box.

SQL Server Performance Condition Alert

You've just configured an event alert. To configure a performance alert, first right-click Alerts, and open the New Alert Properties dialog box. Name the alert "Log full," and under Type, select SQL Server performance condition alert. Doing so will change the options to monitor in the lower part of the dialog box.

Under Object, select SQL Server:Databases, and under Counter, select Percent Log Used. Then under Instance, select a database that you want to monitor for available log space. If you're working on a test system with no production databases, you can use Northwind as your database. (However, Northwind's log typically won't fill up because the Truncate Log on the Checkpoint option is set.) Change the Alert if counter to "rises above," then enter a value of 85 in the Value field to activate the alert before the log completely fills up.

Click the Reponse tab. You would usually run a job here to back up the database and log and truncate the log. For this example, just add a notification to the operator. Change the delay between notifications to 5 or 10 minutes. If you leave the delay at the default of 1 minute and execute a job when this alert fires, it's possible that before a backup job has had time to finish, the alert will fire again and restart the backup job.

Click OK to close the dialog box and complete your performance alert setup.

Testing the Performance Alert

To test the performance alert, change the Northwind database's properties so that data and log files don't grow automatically, then clear Truncate Log on the Checkpoint option. Now run the following:

— Run to create table:CREATE TABLE [dbo].[Order Details2] ([OrderID] [int] NOT NULL ,[ProductID] [int] NOT NULL ,[UnitPrice] [money] NOT NULL ,[Quantity] [smallint] NOT NULL ,[Discount] [real] NOT NULL )

— Open a transactionbegin tran

— repeat next four lines until log full alert fires

insert into [order details2] select * from [order details] where orderid < 10300godelete  from [order details2]

— Cleanupcommitcheckpointbackup log Northwind with truncate_only

You can now turn on the options for Northwind to allow automatic file and log growth and turn on Truncate Log on the Checkpoint option. If you think you might have damaged the Northwind database, make sure no connections are open to it, and drop it. You can then rebuild Northwind by running the instnwnd.sql script from the mssql7install directory.

Next month's lab exercises will cover setting up automated jobs and configuring alerts and operators for those jobs.