How to setup SQL Server alerts and email operator notifications

By:   |   Comments (39)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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.

new sql server operator


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

create new sql server operator

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.

sql server agent 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.
sql server agent properties

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.

restart sql server agent

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

create new sql server 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
create new sql server alert

Click on the Response pane:

  • Check Notify operators
  • Check E-mail for the operator
create new sql server alert

Click on the Options pane:

  • For Include alert error text in select E-mail
  • For Delay between responses enter 2 minutes
  • Click OK
create new sql server alert

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.

sql server alert history

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
disable sql server alert

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.
change sql server alert
  • Click on the Options page
  • Change response to 720 minutes which is 12 hours or whatever value is suitable
  • Click OK
change sql server alert

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, February 8, 2022 - 2:35:31 PM - Greg Robidoux Back To Top (89767)
Hi Gloria, you could probably do that using WMI to check. I don't have a specific article for you to refer to.

-Greg

Tuesday, February 8, 2022 - 2:14:54 PM - Gloria M Vila Back To Top (89766)
Can you set up an alert to notify when the CPU is, let's say at 90%?

Friday, November 6, 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 6, 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 Back To Top (81493)

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 Back To Top (79248)

 Excellent! Thanks for the walk-through...

Steve


Monday, March 4, 2019 - 11:11:07 AM - Greg Robidoux Back To Top (79188)

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 4, 2019 - 10:15:01 AM - RANJEET Back To Top (79187)

 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 Back To Top (79045)

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 8, 2017 - 9:37:09 AM - Andrew Back To Top (67043)

 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 8, 2017 - 9:11:26 AM - Andrew Back To Top (67042)

 

 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 6, 2017 - 6:55:38 AM - Ganesh Thadishetti Back To Top (66968)

 

 Good post


Wednesday, March 8, 2017 - 8:53:21 AM - Billal Back To Top (47584)

 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 Back To Top (40927)

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 3, 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 Back To Top (38081)

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 2, 2014 - 11:43:38 PM - K.Bala Back To Top (34826)

 David Bird, 

Thanks, well explained, very useful


Thursday, August 14, 2014 - 2:57:08 AM - GULAM MUSTHAFA Back To Top (34122)

 

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


Wednesday, July 23, 2014 - 8:03:02 AM - Starsman Back To Top (32839)

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 Back To Top (26494)

 

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 Back To Top (25778)

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 5, 2013 - 5:58:59 PM - Salman Back To Top (21941)

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

Thanks


Wednesday, January 2, 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 Back To Top (18176)

@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 7, 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 Back To Top (16951)

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 Back To Top (1205)

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

 Steve















get free sql tips
agree to terms