Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured

Configure Database Mail

1. Expand the Management node in SQL Server Management Studio

2. Right-click Database Mail and select Send Test Database mail
   If you receive the message that there are no Database mail profiles, you have to create an email profile first

3. When prompted to launch the configuration wizard, click Yes. Another option is to right-click Database Mail and select Configure Database Mail

4. Create a database mail account using your mail server parameters and credentials

   

5. Create a database mail profile and add the mail account created in the previous step

   

Configure SQL Server Agent

Another very important step, often skipped when creating an alert, is setting SQL Server Agent properties:

1. Right-click SQL Server Agent in Object Explorer and select Properties
2. Select the Alert System tab
3. Select the Enable mail profile option
4. Make sure that Mail system is Database Mail
5. Select Mail profile from the drop-down list

6. Make sure that Include body of e-mail in notification message is selected

   

7. The fail-safe operator is a backup operator that will be notified if sending notifications to all other ‘regular’ operators fails. If you want to use this option, select the Enable fail-safe operator option and select the operator from the list
8. Click OK
9. Restart SQL Server Agent, as changes might not be applied automatically

If the mail profile is not enabled for SQL Server Agent, you will be able to create SQL Server alerts and set email notifications successfully. There will be no error messages, nor warnings, yet emails will not be sent. To find out why the email notifications are not sent, you will have to check the SQL Server error logs, they will clearly indicate that the problem is that Database Mail is not enabled for SQL Server Agent notifications

Creating a SQL Server alert

Alerts can be created using SQL Server Management Studio or T-SQL. When creating an alert, you have to specify its name, the event or threshold value that fires the alert, and the action executed when the alert is fired

1. Make sure that SQL Server Agent is running. If not, right click SQL Server Agent in SQL Server Management Studio Object Explorer and select Start. Consider setting SQL Server Agent to start up automatically
2. Right-click the Alerts node in the SQL Server Agent and select New Alert
3. Enter the alert name and select SQL Server performance condition as the alert type from the drop-down menu. Other two available options are SQL Server event and WMI event
4. In the Performance condition alert definition, specify the object name, for example Buffer Manager. Other available objects are Access Methods, Availability Replica, Buffer Node, Catalog Metadata, Databases, General Statistics, Latches, Locks, and many more

   These object types can be obtained by executing

   1 2 3 4 5

   select distinct object_name from sys.dm_os_performance_counters

5. The list of available counters shows all Buffer Memory counters. Again, you can see them when querying the sys. dm_os_performance_counters view, or in Performance Monitor. In this example, we’ll select Page Life Expectancy
6. The instance name is the database name, Total if the SQL Server alert is applied to all databases, counter specific information, or blank. This is the information returned in the instance_name column by the sys. dm_os_performance_counters view
7. The Alert if counter field can have falls below, becomes equal to, or rises above values

   As the normal values for Page Life Expectancy are above 300 seconds (5 minutes), we’ll select falls under and enter 300 in the Value field

   

   Note that the counters are queried periodically and that there might be a slight delay before the alert is triggered

   Here is an example for setting Memory Grants Pending. Its value represents the total number of SQL Server processes waiting to be granted workspace in the memory. The threshold value is zero. If any processes are waiting for the memory workspace, they are queued and the Memory Grants Pending value is higher. The alert will be fired if the counter rises above 0

   

8. In the Response tab, specify what event type you want to trigger by the alarm. Available options are to execute a SQL Server job, and to send a notification to an operator. You can select both for a single SQL Server alert. To be able to create a response, you must be a member of the sysadmin role
9. To execute a job, select the Execute job option, and an existing job from the drop-down list. If the job doesn’t exist, click the New Job button and create a new job

10. To send a notification, select Notify operators

    

11. If the operator list is empty, select New Operator and fill in the records

    

12. In the Notifications tab, select E-mail for the alert type

    Another way to add a notification is using the sp_add_notification stored procedure

    1 2 3 4 5 6 7 8 9

    USE [msdb] GO   EXEC dbo.sp_add_notification @alert_name = N'PLE_Alert', @operator_name = N'Millie', @notification_method = 1 GO

    The @notification_method parameter can be 1,2, or 4 for emails, pager, and net send, respectively. Only members of the sysadmin SQL Server role can execute the sp_add_notification stored procedure

13. Click OK and the new Operator will appear in the Operator list of the Alert Properties dialog
14. Open the Options tab in the Alert Properties dialog

15. Select to include the error message into emails sent and add additional text that will provide more details about the situation that triggered the alert

    

16. Set the delay between responses. The default is 0 minutes and 0 seconds. Keep in mind that sending frequent email notifications until the issue is fixed and the condition doesn’t trigger the alarm might lead to sending too many emails, block your email client and affect the complete system performance

17. Open the History tab, to see how many times the SQL Server alert has occurred, the last occurrence, and the last response to the alert. To reset the number of captured alerts and other parameters shown in this tab, check the Reset count check box

    

The same alert can be created using T-SQL and the dbo.sp_add_alert stored procedure. Note that it has to be executed against the msdb database by a member of a sysadmin role

When an alert is fired on the FUJITSU\SQL2012 instance, you’ll get an email with the SQL Server Alert System: ‘PLE_Alert’ occurred on \\FUJITSU\SQL2012 subject and the following text in the email body:

DATE/TIME: 2/19/2014 1:08:41 PM

DESCRIPTION: The SQL Server performance counter ‘s’ (instance ‘s’) of object ‘s’ is now below the threshold of s (the current value is s).

COMMENT: Page Life Expectancy has fallen under 300 seconds

JOB RUN: (None)

Disabling an alert
To stop the alert from being fired, but still keep it as inactive, disable it. You can easily enable it and reuse later

1. Expand the SQL Server Agent node in SQL Server Management Studio Object Explorer
2. Select the alert you want to disable and right-click it
3. Select Disable

The same can be done using T-SQL. Only members of the sysadmin SQL server role can execute the sp_update_alert stored procedure

When set to 0, the @enabled parameter disables the alarm. To enable the alarm, use same code except for @enabled = 1

Note that all T-SQL commands given in this article, unless qualified as msdn. dbo.<procedure_name> have to be executed against the msdb database. Otherwise, you’ll get the error message that the procedure cannot be found

As shown, the Alert feature in SQL Server Agent is very useful for automating performance issue notifications. It enables creating SQL Server alerts for every available SQL Server performance counter. For each counter, you can set a custom threshold value and specify whether the expected values should be higher or lower. However, it is not possible to set two values for a counter in the same alert. For example, if the counter value is lower than the value A – fire an alarm with a notification about a medium severity issue, and if the counter value falls below the value B – fire a critical alarm

Email notifications can be seamlessly sent to a number of operators, or to a mailing list. Also, sending pager and net send notifications are available, but will be depreciated in the future SQL Server versions

Setting multiple alarms is not very user friendly, as you have to create a single alarm for each counter you want to monitor, therefore go through a number of steps. The feature is supported only in Enterprise and Business Intelligence SQL Server editions

• Author
• Recent Posts

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic

Latest posts by Milena Petrovic (see all)

• Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports - September 12, 2014
• Using custom reports to improve performance reporting in SQL Server 2014 – the basics - September 8, 2014
• Performance Dashboard Reports in SQL Server 2014 - July 29, 2014