Programming4us
         
 
 
SQL Server

SQL Server 2008 Scheduling and Notification : Managing Alerts

7/10/2011 3:36:59 PM
The SQL Server Agent can monitor events that occur on the database server and automatically respond to these events with alerts. Alerts can be fired based on SQL Server events, performance conditions, and Windows Management Instrumentation (WMI) events. After an alert is fired, the SQL Server Agent can respond by notifying an operator or executing a job. This provides a proactive means for identifying and reacting to critical conditions on a database server.

Defining Alert Properties

To define alerts, you select the SQL Server Agent node in the Object Explorer tree and then right-click on the Alerts node and select New Alert. Figure 1 shows an example of the New Alert dialog that appears.

Figure 1. The General page of the New Alert dialog.

The General page selected in Figure 1 allows you to define the basic alert properties, including the name of the alert and type of event you want the alert to respond to. The default type of alert, the SQL Server event alert, is triggered by SQL Server events that write to the Windows Application event log. SQL Server writes to the Application event log when the following events occur:

  • When sysmessages errors with a severity of 19 or higher are generated. You can use the sys.sysmessages catalog view to view all the sysmessages that are stored in the server. You can create new user-defined messages by using the sp_addmessage stored procedure; they must have a msg_id (or error number) that is greater than 50,000. The error message must be created before you can reference the error number in an alert.

  • When sysmessages errors are generated by the database engine. These messages have error numbers lower than 50,000 and are installed by default.

  • When any RAISERROR statement is invoked with the WITH LOG option. The WITH LOG statement forces the event to be written to the Application event log. Messages generated with RAISERROR that have a severity level greater than 18 are required to write to the Application event log.

  • When sysmessages have been altered with the sp_altermessage statement to write to the application log. The sp_altermessage command has a write_to_logsys.messages. When the write_to_log parameter is set to WITH_LOG, these message automatically write to the Application event log, regardless of whether the WITH_LOG option is used when the error is raised. parameter that you can use to modify error numbers found in

  • When application calls are made to xp_logevent to log an event to the application log.

The bottom portion of the General page of the New Alert dialog allows you to define which events in the Application event log the alert should respond to. You can have the event respond to a specific error number, the error severity level, or specific text that is contained in the error message. The sys.sysmessages catalog view contains a complete list of all the error message details for all the supported languages. You can use the following SELECT statement to list the error messages for the English language:

SELECT * FROM SYS.SYSMESSAGES
where msglangid = 1033
order by msglangid, error

You can define an alert for hundreds of messages. For example, you can define an alert that responds to changes to database options. You do this by selecting error number 5084, which is triggered whenever a change is made to the database options. You can also narrow the scope of the alert to look at a specific database by using the Database Name drop-down. This limits the alert to errors that occur in the specific database you choose. The default option is to look at all databases.

The two other types of alerts you can define are SQL Server performance condition alerts and WMI event alerts. A SQL Server performance condition alert reacts to performance conditions on the server. Figure 2 shows an example of this type of alert.

Figure 2. A SQL Server performance condition alert on the General page.

When you select a SQL Server performance condition alert, you need to select the performance object and counter for that object to monitor. The SQL Server performance objects and counters available on the General page of the New Alert dialog are a subset of those available in the Windows Performance Monitor application. These performance metrics encompass key indicators, such as memory, CPU, and disk space.

After selecting the object and counter, you need to define the performance threshold for the alert at the bottom of the General page, below the Alert if Counter label. In the example shown in Figure 16.16, the alert is monitoring the transaction log file for the AdventureWorks database. The threshold has been set such that the alert will fire if the transaction log for this database rises above 2KB.

The WMI event alerts use WMI to monitor events in an instance of SQL Server. The SQL Server Agent can access SQL Server events by using the WMI provider for server events by issuing WMI Query Language (WQL) statements. WQL is a scaled-down version of SQL that contains some WMI-specific extensions. When a WMI query is run, it essentially creates an event notification in the target database so that a related event will fire.

Figure 3 shows an example of a WMI event alert. This example uses a WQL query that detects any Data Definition Language (DDL) changes to any of the databases on the server. After the alert is created, you can test it by running a DDL statement against the database (for example, alter table Person.address add newcol int null).

Figure 3. The General page showing a WMI event alert.

Defining Alert Responses

The definition of an alert has two primary components. The first component involves the identification of the event or performance condition that will trigger the alert. The second part of an alert definition involves the desired response when the alert condition is met. You can define an alert response by using the Response page on the alert’s Properties screen. Figure 4 shows a sample response that has been configured to use NET SEND on a message to the operator named ChrisG.

Figure 4. Configuring an alert response.

Operator notification and job execution are the two responses to an alert. Operator notification allows for one or more operators to be notified via email, pager, or the NET SEND command. Job execution allows for the execution of a job that has been defined in the SQL Server Agent. For example, you could execute a job that does a database backup for an alert that is triggered based on database size. You can define both job execution and operator notification in a single alert; they are not mutually exclusive.

 

You can further define an alert response by using the Options page of an alert’s Properties window (see Figure 5).

Figure 5. Alert options.

You can include an alert’s error text in the operator notification message on this page. This alert error text provides further details about why the alert was fired. For example, if you have an alert that is triggered by changes to database options, the alert error text would include the actual option that was changed. You can also define additional notification text that is included when the message is sent. This message could include directives for the operators or additional instructions. Finally, you can define the amount of time that the alert will wait before responding to the alert condition again. You do this by using the Delay Between Responses drop-downs (Minutes and Seconds) to set the wait time. This capability is useful in situations in which an alert condition can happen repeatedly within a short period of time. You can define a response delay to prevent an unnecessarily large number of alert notifications from being sent.

Other -----------------
- SQL Injection Attacks and Defense : Accessing the File System (part 2) - Writing Files
- SQL Injection Attacks and Defense : Accessing the File System (part 1) - Reading Files
- SQL Server 2008 Scheduling and Notification : Managing Jobs
- SQL Server 2008 Scheduling and Notification : Managing Operators
- SQL Server 2008 Scheduling and Notification : Configuring the SQL Server Agent
- SQL Server 2008 : Database Mail - Related Views and Procedures
- SQL Server 2008 : Database Mail - Using SQL Server Agent Mail
- SQL Server 2008 : Sending and Receiving with Database Mail
- SQL Server 2008 : Setting Up Database Mail
- SQL Server 2008 : Security and Compliance - Setting Up Auditing via T-SQL & SQL Injection Is Easy to Do
- SQL Server 2008 : Security and Compliance - SQL Server Auditing
- SQL Server 2008 : Security and Compliance
- SQL Server 2008 : Transparent Data Encryption
- SQL Server 2008 : Data Encryption - Column-Level Encryption
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- SQL Server 2008 : Data Encryption
- SQL Server 2008 : Client Data Access Technologies
- SQL Server 2008 : Client Configuration
- SQL Server 2008 R2 : Client Installation
- SQL Server 2008 R2 : Client and Server Networking Considerations
 
 
Most View
- Exchange Server 2010 : Fundamentals and Components of Federated Delegation (part 4)
- SharePoint 2010 : Rename a List or Document Library or Change Its Description
- SharePoint 2010 : Tag an External Site
- Windows Azure : Peeking Under the Hood with a Command Shell (part 2) - Running the Command Proxy
- Programming Excel with VBA and .NET : Tasks in Visual Basic - Find Truth & Compare Bits
- Planning a Windows SBS 2011 Deployment
- ASP.NET Security : The Membership and Role Management API (part 3) - Role
- Developing an SEO-Friendly Website : Creating an Optimal Information Architecture (part 4)
- Windows Home Server 2011 : Controlling Services (part 2) - Controlling Services at the Command Prompt, Controlling Services with a Script
- Windows Phone 7 : Listening to FM Radio
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS