Minette Steynberg

How to create and configure SQL Server Agent Alerts

October 24, 2016 by

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.


Figure 1: User must be a member of the sysadmin fixed server role

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.


Figure 2: Accessing the Alerts options

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


Figure 3: Create SQL Server event alert

Update the following details for the alert:

  • Name

  • Type
    In this case, we will user a SQL Server event alert

  • Database

  • 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:

  1. Execute a job

  2. 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.


Figure 4: Configure response

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.


Figure 5: Create a SQL Server performance condition alert

Update the following details for the alert:

  • Object

  • Type
    In this case, we will user a SQL Server performance condition alert

  • Counter
    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 here

  • Alert if counter
    Specify the counter threshold, if it reaches a specific value.


Figure 6: Backup transaction log in response to alert

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 alert

  • Namespace
    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.


Figure 7: Create a WMI event alert

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.


Figure 8: Access WMI Tester

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


Figure 9: Windows Management Instrumentation Tester

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.

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey

Resources


Minette Steynberg

Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg
Minette Steynberg
SQL Server maintenance

About Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups. Minette currently works as a Data Platform Solution Architect at Microsoft South Africa. View all posts by Minette Steynberg

2,038 Views