Introduction
If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.
Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?
SQL Server Agent Alerts are what you need.
How can alerts help me?
You are probably asking why you need to configure alerts when you want a job to be executed? After all you want this to happen without human intervention. The truth is that SQL Server Agent Alerts can do more than just send alerts.
In fact, it has the option to either send an alert or to execute an existing job or both.
Alert types
SQL Server Agent supports 3 types of alerts:
SQL Server event alerts
This fires an alert when a specific SQL Server even occurs, such as when a specific error number occurs, or a specific severity level.SQL Server performance event alerts
This fires an alert when a performance counter reaches the specified threshold, such as if the average wait time for the Network IO wait statistic exceeds a certain value.WMI event alerts
This fires an event in response to a Windows Management Instrumentation (WMI) event, such as when a new file appears in a specific folder.
Security
In order to create an alert, you have to be a member of the sysadmin group, since the procedure sp_add_alert can only be executed by a member of sysadmin.
Creating a SQL Server Agent Alert
This functionality can be accessed by going to SQL Server Agent in SQL Server Management Studio, and right clicking on the on the Alerts node.
The New Alert dialog contains 4 pages:
General
This is where you configure the basic details of the Alert such as the name and alert type.Response
This is where you configure what should happen in response to the event.Options
Allows you to specify what should be included in the messages, and how long the delay between the responses should be.History
Displays the history of the alerts such as when the last one occurred, if there was a response , how many times it has occurred and allows you to reset the count.
Creating a SQL Server event alert
Creating a SQL Server event alert is useful in cases where you want to perform an action in response to a specific error or error severity.
In example: Let’s say an error with a severity level of 23 occurs, indicating that there may be a hardware or software problem which may have left your database in an inconsistent state. You may want to run DBCC CHECKDB to determine the extent of the problem.
To do this click on New Alert…
The New Alert Dialog appears
Update the following details for the alert:
Name
Type
In this case, we will user a SQL Server event alertDatabase
-
Alerts will be raised on option
In this case, we are using Severity and selecting severity level 23. Alternatively, you can specify an Error number or a specific word or phrase contained within the error message.
Once the Alert details are configured we will configure what should happen when this event occurs. We do that by going to the Response page.
There are two potential options for a response:
Execute a job
Notify an operator or operators
These options are not mutually exclusive. You are able to execute a job and notify an operator if you wish to do so.
In this case, we will execute a job to check the consistency of the database in question.
Creating a SQL Server Performance Condition alert
This alert type will be used if you want to create an alert based on a specific performance condition. This could be something like if the Page Life Expectancy hits a certain value or if the Queue Length becomes too long.
In this example, I will setup an alert which will fire when the transaction log used space rises above 90%, which will then execute a job which will attempt to back up the log. If you want to, you can always include some functionality to check that the log file got truncated after the backup and notify someone in case there is a specific reason as to why the log is not getting truncated. The details and complexity of the logic is up to you.
Update the following details for the alert:
Object
Type
In this case, we will user a SQL Server performance condition alertCounter
The performance counter you are interested in, such as the Percentage log used.Instance
Since we specified database as the object, we should select the database name hereAlert if counter
Specify the counter threshold, if it reaches a specific value.
Create a WMI event alert
The WMI event type allows you to configure an alert if a specific event occurred in Windows. In this example, I will create an event which fires when a file is dropped into a specific folder.
Update the following details for the alert:
Name
Type
In this case, we will specify the WMI event alertNamespace
This must be a namespace on the same computer on which SQL Server Agent is running. In this case, it will be root\cimv2-
Query
This is the WQL query for which the alert should be triggered. If a file appears in the C:\WMITest folder the event will be triggered. The WITHIN 5 indicates the polling interval, which is set to 5 seconds here.
WQL queries can be a bit daunting if you are not used to using them. There is a nifty tool which ships with Windows which can help you to test your WQL queries. You can access it by going to Run and typing wbemtest.
This tool will help you to test your WQL queries. If you are interested in more detailed information on this tool, please read this excellent article WMI Query Language by Example
Conclusion
The SQL Server Agent alert framework allows for a lot of flexibility in terms of notification and reactive responses which can be extremely useful in cases where a known recurring issue requires a known action in response.
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg
- The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018
- 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017
- Introduction to Azure SQL Data Warehouse - August 29, 2017