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.
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).
Figure 9-10. Operators Node inside Object Explorer.
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.
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.
Tips and Tricks
SQL Agent is constantly polling the Windows Event Log for SQL Server Database Engine-generated events and tries to match event instances with criteria specified for each alert. Therefore, any alert SQL Agent needs to respond to can be viewed directly if you use the Windows Event Viewer tool. You can find this tool inside the Windows Administrative Tools folder (see Figure 9-13).
Figure 9-13. Windows Event Viewer.
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.
Tips and Tricks
In SQL Server 2000, predefined alerts shipped with SQL Agent. These alerts were removed from SQL Server 2005 for various reasons having nothing to do with functionality. One of the alerts that existed in SQL Server 2000 and that is still considered good practice to use today was one that monitored events with error number 9002 for your database, indicating that the transaction log is full and needs to be backed up.
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.
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.
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.
Tips and Tricks
To see the current performance counter value you can select data from the sys.sysperfinfo system view. This is the same mechanism used by the SQL Agent alerting engine when performing periodic polling of the counters to determine when an alert needs to be raised.
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.
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.
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