Using SQL Server Agent Monitoring Capabilities

SQL Server Agent provides monitoring capabilities through its internal alerting engine. It supports the following three types of alerts.

• SQL Server Event alerts

• SQL Server Performance alerts

• Windows Management Instrumentation alerts

In response to any alert, SQL Agent can start a job or send a notification via email or pager, or through use of a net send command. The following sections define each type of alert and consider its best application. However, prior to defining the first alert it's best to create an operator to be notified when an alert gets triggered.

SQL Server Agent Operator

To define an operator, expand SQL Server Agent in SQL Server Management Studio Object Explorer and right-click on Operators (see Figure 9-10).

Selecting New Operator brings up a New Operator dialog (see Figure 9-11). Assume the operator has a valid email address and carries a pager during normal business hours. Later, when selecting the notification method for an alert, you can indicate the preferred way to contact the person.

Figure 9-11. New Operator dialog.

[View full size image]

Notice how the pager information shows up in regular email format. SQL Agent uses the same mailing capabilities (either internal MAPI-based component or Database Mail solution), based on the configuration, for both kinds of delivery.

SQL Server Event Alerts

Now you are ready to define a SQL Server Event Alert. You can launch the New Alert dialog (see Figure 9-12) by right-clicking the Alerts node in object Explorer.

Figure 9-12. New Alert dialog.

[View full size image]

Figure 9-13. Windows Event Viewer.

[View full size image]

Virtually any event that has MSSQLSERVER specified as an event source can be intercepted by SQL Agent.

You can define an alert based on a specific error number or severity. The error number actually corresponds to a message_id column in the sys.messages system view and the Event column as displayed by Windows Event Viewer.

If you want to raise a more generic alert based on the severity of the message, you can also do that by selecting the appropriate radio button. There is a pre-defined list of error severity levels supported by the system. Table 9-2 lists general ranges for different severity levels and what they mean.

Table 9-2. Error Severity Level Ranges

Severity level	Description
0-10	Indicate informational messages and non-severe errors
11-16	Indicate errors that can be corrected by the user
17-19	Indicate software errors that cannot be corrected by the user
20-25	Indicate system problems and fatal errors

Another important option when defining an alert is the capability to specify an exact string that must be found inside event message text for an alert to be raised. You enable this option by clicking the check box in the lower part of the dialog, and it should help you further filter out unwanted events.

On the Response tab of the New Alert dialog you can select previously defined operators and a desired notification method (see Figure 9-14).

Figure 9-14. New Alert dialog Response tab.

[View full size image]

In addition, you can also select a job to run, possibly to compensate for the event action or to provide other notification not directly supported by SQL Agent infrastructure. Response tab options are common for all types of SQL Agent alerts, but specifics of an alert definition vary with its type.

SQL Server Performance Alerts

SQL Agent will monitor SQL Server Database Engine-related performance counters and compare them to pre-defined alerting thresholds.

Figure 9-15 defines an alert on the total number of lock timeouts in the SQL Server system. The alert is to fire when a selected performance counter value exceeds 100 lock timeouts per second. Other possible conditional clauses in addition to "rises above" include "falls below" and "becomes equal to," which together essentially cover all possible scenarios.

Figure 9-15. Creating a new performance alert dialog.

[View full size image]

Windows Management Instrumentation Alerts

Windows Management Instrumentation (WMI) alerts are possible only starting with SQL Server 2005. To define a WMI alert you need to know how to write WQL queries. Let's define an alert that will monitor SQL Server Database Engine for creation of a new database. This alert needs to fire automatically, creating a backup job for new databases in addition to sending a notification message to the previously defined operator.

Go to the already familiar dialog for alert creation (see Figure 9-16) to create an alert.

Figure 9-16. Windows Management Instrumentation Alert properties.

[View full size image]

Notice when the dialog first shows up that the WMI namespace name is automatically filled in by the tool based on the specific Database Engine instance name. The query only looks like T-SQL; in reality it is in WQL.

Now it's time to write and execute the next few required steps directly in the Object Editor window. You could do everything with management dialogs, but job creation was already covered in the previous chapter, so this demonstration uses T-SQL directly.

The first task is to add a backup device for this instance of SQL Server.

USE [master]
GO
exec master.dbo.sp_addumpdevice @devtype = N'disk',
        @logicalname = N'MyBackups',
        @physicalname = N'C:\Backups\MyBackups2.bak'
GO

Next you need to create a job that is going to execute required T-SQL to create another job. Notice how it obtains the WMI property value DatabaseName dynamically during execution.

use msdb
exec sp_add_job @job_name=N'Automatically create a backup job'
GO
exec sp_add_jobstep @job_name=N'Automatically create a backup job',
            @subsystem=N'TSQL',
            @database_name=N'msdb',
            @on_success_action=3,
            @step_name=N'Create Backup Job',
            @command=N'sp_add_job @job_name=N''Backup $(WMI(DatabaseName))'''
GO
exec sp_add_jobstep @job_name=N'Automatically create a backup job',
            @subsystem=N'TSQL',
            @database_name=N'msdb',
            @on_success_action=3,
            @step_name=N'Add Backup Step',
            @command=N'sp_add_jobstep @job_name=N''Backup $(WMI(DatabaseName))'',
            @step_name=N''Issue BackupCommand'',
            @database_name=N''msdb'',
            @command=N''BACKUP DATABASE $(WMI(DatabaseName)) TO MyBackups'''
GO
exec sp_add_jobstep @job_name=N'Automatically create a backup job',
            @subsystem=N'TSQL',
            @step_name=N'Give job a jobserver',
            @database_name=N'msdb',
            @command=N'sp_add_jobserver @job_name=N''Backup $(WMI(DatabaseName))'''
GO
exec sp_add_jobserver @job_name=N'Automatically create a backup job'
GO



Now we are ready to associate our previously created alert with the
job as follows.


EXEC msdb.dbo.sp_update_alert @name=N'Database Creation Alert',
             @job_name=N'Automatically create a backup job'
GO

If you create a new database on the server, you will immediately notice creation of the job.