Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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.
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.
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:
- Replication: Agent Success
- Replication: Agent Failure
- Replication: Agent Retry
- Replication: Expired Subscription Dropped
- Replication: Subscriber Has Failed Data Validation
- Replication: Subscriber Has Passed Data Validation
- Replication: Subscriber Reinitialized After Data Validation
- Demo: Full msdb Log
- Demo: Full tempdb
- Demo: Sev. 19 Errors (Fatal Error, system will halt)
- Demo: Sev. 21 Errors (Fatal Error, system will halt)
- Demo: Sev. 22 Errors (Fatal Error, system will halt)
- Demo: Sev. 23 Errors (Fatal Error, system will halt)
- Demo: Sev. 24 Errors (Fatal Error, system will halt)
- Demo: Sev. 25 Errors (Fatal Error, system will halt)
InformIT Articles and Sample Chapters
Still using SQL Server 7? More here.
The official Alert description is here.