Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 82 of 361 Next >

SQL Server Alerts

Last updated Mar 28, 2003.

Whenever you hear about SQL Server Alerts, it's important to ask "which kind?" You'll find references out on the web and in books that deal with alerts on two fronts. One is the alerting function you can set within the Windows System Monitor (although it's also called Performance Monitor, which is in fact another, older tool) that can react to thresholds and so on. You can monitor SQL Server Objects and Counters, and set alerts in the System Monitor on the values they return. This alerting feature that can run programs or send messages based on the values form the objects it monitors. Many people use this function, which I've described in another tutorial, and it works out quite well. Because they are dealing with SQL Server Objects and Counters, you'll hear them referred to as SQL Server Alerts — but in fact they are System Monitor Alerts.

The other type of SQL Server Alerts is found within the SQL Server Agent feature. These Alerts react only to SQL Server Engine results, and are used in conjunction with the Operators, Jobs and Messages that SQL Server controls. That's the focus of this tutorial. Although the messages the Alerts respond to are called "Errors", the message generated by the engine (or ones that you create, more later on that) don't actually have to be an error condition. I wouldn't have picked that terminology, but once you understand how the Alert is raised it's easy to substitute "message" for "error" in your mind. By default, errors in the engine are only recorded in the SQL Server Error Logs and the Windows Application Event Log.

Since the Alerting feature is part of the SQL Server Agent feature, you need to have that configured and running for the messages (errors) to raise an Alert condition. I've explained a little more about the SQL Server Agent in another tutorial. The point is that you should have a Windows account with the proper rights and permissions set up for the SQL Server Agent Service (SQL Server 2005 does this for you automatically), and the service should be set to start automatically. You should also have at least one Operator defined, and an Operator defined as the failsafe for the system.

Uses of SQL Server Alerts

Before we talk about the built-in Alerts and how to create new ones, let me explain why you would create your own, and how you can use them in your system.

In effect, and Alert is something that is raised based on a message from the system — sometimes that's an error condition, and other times it's a custom condition you create. When the condition is raised, the Alert can forward that information on to an Operator, or run some custom code.

In a way, Alerts are the brothers to scheduled Jobs. In a SQL Server Job, you can set an action to be performed on a periodic basis, which is an active way of controlling what happens to your server. With an Alert, you can set an action to occur based on a condition. This allows you to make your system reactive to events that are raised on it. Interestingly, you use a Job to perform actions from an Alert.

Perhaps you want to watch the drive space on a server. You would like to know if you're getting close to running out of room on the drive, so you could create code that checks this drive space from time to time. If the drive gets within 20% of being full, you could write a custom error message. This message can raise an Alert, which in turn enters the event in the logs and runs a Job that copies the last backup from the drive and deletes the file to reclaim some space. You could even have the Job page you if you're concerned that the system would go off-line because of the low space condition.

Just about any condition you can think of can be handled by an Alert. An Alert can use the error codes that are built into SQL Server or you can create your own messages. You can find out the error message numbers that Microsoft includes by running this query on either version:

SELECT * 
FROM sysmessages;
GO

You can create your own error messages by setting the number higher than 50,000. I'll show you an example of this in a moment.

Creating Alerts

Before we create an Alert, we need the other two parts of the process: the error message and the Job that will run when the alert fires. You can create all of these objects using the graphical tools in SQL Server, but to save time between the two versions, here is some code that will work on both.

Open either Query Analyzer (SQL Server 2000) or SQL Server Management Studio with a Query Window (SQL Server 2005) and run the following sample code to create a simple job that returns the version of SQL Server that is currently running (Note: You'll need to change the login name here to reflect your own):

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N’TestJob’, 
    @enabled=1, 
    @notify_level_eventlog=3, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N’Test Job for Alert Response Demonstration.’, 
    @category_name=N’Database Maintenance’, 
    @owner_login_name=N’SQL1\Administrator’, @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N’TestJob’;
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N’TestJob’
, @step_name=N’Return Version Level of SQL Server’
,     @step_id=1, 
    @cmdexec_success_code=0, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @retry_attempts=0, 
    @retry_interval=0, 
    @os_run_priority=0, @subsystem=N’TSQL’, 
    @command=N’SELECT @@VERSION;
GO’, 
    @database_name=N’master’, 
    @flags=0;
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N’TestJob’
,     @enabled=1, 
    @start_step_id=1, 
    @notify_level_eventlog=3, 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @delete_level=0, 
    @description=N’Test Job for Alert Response Demonstration.’, 
    @category_name=N’Database Maintenance’, 
    @owner_login_name=N’SQL1\Administrator’, 
    @notify_email_operator_name=N’’, 
    @notify_netsend_operator_name=N’’, 
    @notify_page_operator_name=N’’;
GO

Now run this code that creates an error message:

USE master
GO
EXEC sp_addmessage 50001
, 16
, N’The version of SQL Server has been requested.’;
GO

Let's dissect that a little. The sp_addmessage stored procedure adds the message to the sysmessages table properly. The 50,001 number is the unique message number that you must provide — so the next one could be anything higher. The 7 is the severity level of the message. 1-16 are available for your use, and Books Online explains what each means. I chose 16. I've followed that with some text, which will end up in the Logs and on the screen.

With those prerequisites completed, the process for creating an Alert is roughly the same for both SQL Server 2000 and 2005. The Alert object is underneath the SQL Server Agent node, but the SQL Server Agent node in the tree is slightly different between the two; for SQL Server 2000 it's under the Management node and for SQL Server 2005 it's under the main node.

Once you're in the node you can right-click the Alerts object and select New Alert from the menu that appears. In SQL Server 2000 it's a series of tabs that we'll fill out, and in SQL Server 2005 it's a series of Property Pages. Both have similar formats once you're inside.

First, you'll need to name the Alert, which can be anything you like. Second, you need to pick the type of Alert, which can be either a SQL Server event or a reaction to a Performance Counter. The interesting part starts at the number — type in the 50001 number we created and this Alert will fire, or any other existing number to fire the Alert on one of those. Notice that you can also pick a certain severity of error, which as you recall we set to 7. This would get an entire class of errors. You can further refine when the Alert fires based on the database where it occurs or even specific text.

On the next tab or property pages you set the response that you want when the Error fires. For this test select the TestJob job to execute, and if you want a particular Operator to be notified, you can select that as well. Then save the Alert.

When you save the Alert, you'll get an error message that tells you that Informational errors are not logged. Select "Yes" to ensure that the Alert does in fact fire when the message is raised.

If you're following along but would rather have the commands to create the Alert, you can run the following script:

EXECUTE msdb.dbo.sp_add_alert @name = N’TestAlert’
, @message_id = 50001
, @severity = 0
, @enabled = 1
, @delay_between_responses = 60
, @include_event_description_in = 5
, @job_name = N’TestJob’
, @category_name = N’[Uncategorized]’;
GO

This script creates the Alert and enables a notification so that it fires. Now we're ready to create the condition that invokes the message, which fires the Event, which runs the Job. With actual SQL Server errors, the messages are invoked when an error happens. In our case, we'll force the action using the RAISEERROR command in code. Here's a sample:

RAISERROR (50001, 16, 1);
GO

You can look up RAISEERROR in Books Online, but the only thing you need to provide here is the error number (which was the message we created a moment ago), the severity (we used 16), and the "state". This is a mysterious number that you can just set to 1.

By running this code you should get an error message back, which applications are able to consume and do something with, and the Alert should have been fired.

You can check to ensure all of this worked by right-clicking the Job and selecting View Job History from the menu that appears. You should also see an entry in the Windows Application Event Log. In this example that's all that happened, but in your production environment you would have coded the job to do something useful.

Default Alerts

A few moments ago I mentioned that the default behavior for errors is that they are recorded in the SQL Server Error Logs and the Windows Application Event Logs. This is true for both SQL Server 2005 and SQL Server 2000 for all events that are other than informational, but oddly enough SQL Server 2000 goes a bit further and turns on several Alerts. The reason this is no longer true in SQL Server 2005 is that those conditions are handled in a different way, so these Alerts are no longer necessary. Some Alerts are created on SQL Server 2005 if you use replication. The built-in SQL Server Alerts (only the replication ones are for SQL Server 2005) are as follows — and they are fairly self-explanatory: