How to setup SQL Server alerts and email operator notifications

By: David Bird | Updated: 2019-03-04 | Comments (37) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring

Problem

Setting up a SQL Server alert to email an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no email is being sent. So how do you send an alert to an operator?

Solution

Note: This solution involves restarting SQL Server Agent.

Setup SQL Server Database Mail

First setup database mail with a profile named SQLAlerts. The profile can be named anything, but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly. For procedures in setting up database mail, see this post.

Define a SQL Server Operator

Connect to the SQL Server instance using Microsoft SQL Management Studio. Expand the SQL Server Agent tree and right click on Operators and select New Operator... as shown below.

Specify an operator Name, E-mail name, and click OK

Setup SQL Server Agent Settings

Note: This step is often overlooked when creating a SQL Server alert for the first time. SQL Server Agent must be setup correctly for operators to receive an alert e-mail.

Right click SQL Server Agent and select Properties.

Select Alert System in the left pane and do the following:

Check Enable mail profile
Verify Mail system is Database Mail
Verify Mail profile is SQLAlerts that we discussed above
Check Include body of e-mail in the notification message
Click OKto save settings.

Restart SQL Server Agent to Activate Settings

Warning: Restarting SQL Server Agent will cancel any executing jobs, so this should be done when there is no activity.

Define a SQL Server Alert

This sample SQL Server alert will send an email when the TempDB database gets larger than 0 KB. Note: I am setting this to zero, so the alert can fire. Once verified that this works, you will need to update the setting to a number that makes sense for your environment or disable this alert.

Right click Alerts and select New Alert...

On the General pane specify:

Name: TEMPDB Growing
Type: SQL Server performance condition alert
Object: SQLServer:Databases
Counter: Data File(s) (KB)
Instance: tempdb
Alert if counter: rises above
Value: 0

Click on the Response pane:

Check Notify operators
Check E-mail for the operator

Click on the Options pane:

For Include alert error text in select E-mail
For Delay between responses enter 2 minutes
Click OK

Verify SQL Server Alert is Working

The operator should receive an e-mail if not see the troubleshooting section.

Open the Alert we just setup and click on the History page. The fields should be updated when the alert is triggered as shown below.

Disable the SQL Server Alert Test

To stop the Alert from being sent every two minutes, you have two options: disable the alert or provide a more appropriate size and response times.

To disable the SQL Server alert

You an go to the General page for the alert and remove the checkmark from Enable and click OK
Or you can right click on Alert name and select Disable as shown below

Change SQL Server Alert Settings

To change the settings do the following:

Click on the General page
Change the value to a value larger than the existing TEMPDB database file save. Note: the current file size is included in the e-mailed alert.

Click on the Options page
Change response to 720 minutes which is 12 hours or whatever value is suitable
Click OK

Troubleshooting a SQL Server Alert

If the operator does not receive an e-mail:

Test database mail by sending a test e-mail to the operator's e-mail address.
Make sure you restarted SQL Agent and it is running.

Next Steps

Modify the alert to execute a job that captures all current connections using sp_who2 .
Create alerts for other criteria you wished to monitor.
Using SQL Server Management Studio, you can script the alert so you can easily create it in other instances. See this tip on how to create scripts.

Last Updated: 2019-03-04

About the author

David Bird has years of IT Experience working as a DBA and programmer on Windows, UNIX, and mainframes.

View all my tips

Related Resources

Comments For This Article

Friday, November 06, 2020 - 3:18:45 PM - Greg Robidoux	Back To Top (87768)
Hi Nathaniel, using this approach there doesn't seem to be a way to select a specific data file. Not sure if the message states the file that grew. -Greg

Friday, November 06, 2020 - 2:34:42 PM - Nathaniel	Back To Top (87767)
Hi David, Is there a way to specify the primary mdf file or secondary ndf file(s) when adding a "Data File(s) Size (KB)" Counter alert?

Monday, June 17, 2019 - 6:11:18 AM - Peter

Hi David,

Your article helped me a lot with automation of SQL Server maintenance jobs and alerts :) Really helpful. Thank You.

Tuesday, June 11, 2019 - 3:16:57 PM - oscar	Back To Top (81419)
Hello David - I would like to know if you could help me configure the database server, so that every time someone connects to the database and is different to a specific user send alert emails.

Monday, June 10, 2019 - 4:47:33 AM - Kemal S.	Back To Top (81397)
Thank you David, your post helped me.

Monday, March 11, 2019 - 9:41:37 AM - Steve

Excellent! Thanks for the walk-through...

Steve

Monday, March 04, 2019 - 11:11:07 AM - Greg Robidoux

Hi Ranjeet,

take a look at this tip:

https://www.mssqltips.com/sqlservertip/4173/troubleshoot-sql-server-agent-notifications-and-database-mail/

-Greg

Monday, March 04, 2019 - 10:15:01 AM - RANJEET

I am facing below error while testing mail from sql server 2014.

Error :: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2019-03-04T20:41:27). Exception Message: Cannot send mails to mail server. (Failure sending mail.).

Please help, how to resolve this issue. I have configured all things properly.

Regards,

Ranjeet

Friday, February 15, 2019 - 12:19:21 PM - Panwar

Hi David,

I have one table licence in which start date and end date column. I want to send an email notification to that user before 7 days that licence is about expired. Can you please help me.

Thanks.

Thursday, December 14, 2017 - 10:16:41 AM - Susan Qi	Back To Top (74008)
Excellent work! Thank you.

Sunday, October 08, 2017 - 9:37:09 AM - Andrew

Hello,

I have removed alert for job notification and have deleted job but it keeps sending in sql server 2014. How do I stop it?

Sunday, October 08, 2017 - 9:11:26 AM - Andrew

I am trying to disable system notifications in sql server 2014 but it will not stop. I have deleted the job, operator and disabled db email. What else can I do to stop the notifications from sending to my email?

Friday, October 06, 2017 - 6:55:38 AM - Ganesh Thadishetti

Good post

Wednesday, March 08, 2017 - 8:53:21 AM - Billal

Hi David

Thanks! Nice and simple explanation.

Saturday, April 23, 2016 - 1:45:45 PM - Any	Back To Top (41317)
THANK A LOT FOR THIS NICE TIP.

Monday, March 14, 2016 - 3:32:50 AM - Robert

Hi David,

Thanks for your excellent post.

I need a help for you. I'm trying to test the SQL Server mail but I can't configured production SMTP mail information so is it possible to send the mail configuration for some mail drop folder on local system? So that I can go to the maildrop folder and check the mail sent from the SQL SERVER

Thanks,

Robert

Thursday, December 03, 2015 - 3:51:43 AM - Nacho Martín	Back To Top (40188)
Still working for SQL server 2012. Thanks!

Tuesday, June 30, 2015 - 10:47:19 AM - Janet

hi David

Thank you for the post.

I have done everything but still not getting emails. What have I overlooked? I can send a test email.

Thursday, December 18, 2014 - 10:19:36 AM - Jigglypuff	Back To Top (35662)
My question is if you do not restart the SQL Server Agent and have the 'Test' alert run what would the result be? Will the history section of the alert properties still fill up and not receive an error? Is restarting the SQL Server Agent crucial?

Thursday, October 02, 2014 - 11:43:38 PM - K.Bala

David Bird,

Thanks, well explained, very useful

Thursday, August 14, 2014 - 2:57:08 AM - GULAM MUSTHAFA

i want to put some querires on these settings it that possiblet

Wednesday, July 23, 2014 - 8:03:02 AM - Starsman

Thank You so much David , this post was very useful to me .

Monday, May 26, 2014 - 3:12:03 AM - Munnan	Back To Top (31941)
this step by step help me configure my first MS Sql Server Email Alert. Thanks for wonderful Work ....

Thursday, January 30, 2014 - 1:50:30 AM - Krishna Reddy Y	Back To Top (29277)
Thank you David. Excellent and still relevent in 2014! Cheers! :)

Tuesday, November 12, 2013 - 6:32:11 PM - Luis Vega	Back To Top (27475)
Sr thank you very much!.

Tuesday, August 27, 2013 - 11:55:08 AM - Jessica

hello, for the example alert you mention above instead of putting the value to be a set number of the size, can we enter a percent for example 10%

i would like to get alert it when tempdb data file size rises above 10% above its current size.

Wednesday, July 10, 2013 - 11:52:22 AM - Ron

I have done all of these things and still cannot get the operator notification either via jobs or alerts to work. I don't have any trouble on our SQL 2008 servers. Can anyone point out anything I may be overlooking.

Thanks,

Thursday, June 13, 2013 - 12:33:12 PM - Subrat	Back To Top (25422)
Thanks a lot sir.

Wednesday, May 15, 2013 - 10:47:12 AM - Paul	Back To Top (23962)
I have all of this working with the Alerts but when a Maintenance Plan runs it will not send out an email.

Tuesday, February 05, 2013 - 5:58:59 PM - Salman

Good one. But if I have to modify the text (body/subject). How can I do that.

Thanks

Wednesday, January 02, 2013 - 1:22:02 PM - Jodi	Back To Top (21235)
I have gone through the steps to set up the operator, but when I go to the SQL Server Agent properties and go to the Alert System page, I enable the maile profile, select Database Mail, but then the profile I created is not listed. I have tried restarting and it doesn't change anything. What do I need to do to fix this?

Monday, December 17, 2012 - 2:08:19 PM - Matthew	Back To Top (20989)
Thank you David. Excellent guide and still relevent in 2012! Cheers! :)

Friday, June 22, 2012 - 7:14:34 AM - Reuben Sultana

@carlos

You might want to refer to the following article: SQL Server Diaries: Send SQL Server Agent Job log file by Email

@Bharath

The delimiter is a semi-colon (;) but the length is limited to 256 characters.

Reuben

Thursday, June 07, 2012 - 4:29:59 AM - Bharath	Back To Top (17823)
Can we have a multiple email ids configured as one operator? If so, what is the delimiter between the emails ids?

Monday, April 16, 2012 - 6:08:46 PM - carlos

Good article, thanks.

I have a job with notifications when it fails.

When the job fails, I receive an email, but it only says:

JOB RUN: 'test1' was run on 4/16/2012 at 10:35:00 AM
DURATION: 0 hours, 0 minutes, 1 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 15 (prog). The last step to run was step 1 (step1).

How more detail about the error could be sent by email?

Thanks in advance.

Wednesday, August 12, 2009 - 1:32:25 PM - dguillory	Back To Top (3866)
http://www.mssqltips.com/tip.asp?tip=1803

Thursday, June 19, 2008 - 10:15:16 AM - sm8680

Hi does anyone know how to alert for when a job gets disabled? Please advise. Thanks

Steve

How to setup SQL Server alerts and email operator notifications

Problem

Solution

Setup SQL Server Database Mail

Define a SQL Server Operator

Setup SQL Server Agent Settings

Restart SQL Server Agent to Activate Settings

Define a SQL Server Alert

Verify SQL Server Alert is Working

Disable the SQL Server Alert Test

Change SQL Server Alert Settings

Troubleshooting a SQL Server Alert

Next Steps

About the author

Comments For This Article

Recommended Reading