Learn more about SQL Server tools

   
   















































How to setup SQL Server alerts and email operator notifications

MSSQLTips author David Bird By:   |   Read Comments (19)   |   Related Tips: More > Monitoring

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

Solution

Note: This solution involves restarting SQL Agent.

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 Operator

Connect to the instance using Microsoft SQL Management Studio

Double Click SQL Server Agent

Right Click on Operators and select New Operator


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

Setup SQL Agent Settings

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

Right Click SQL Server Agent > select Properties

Select Alert System in the left pane

  • Checkmark > Enable mail profile
  • Verify Mail system: Database Mail
  • Verify Mail Profile: SQLAlerts
  • Checkmark > Include body of e-mail in the notification message
  • Click OK.

Restart SQL Agent to activate settings.

Warning: Restarting SQL Agent will cancel any executing jobs.

Define Alert

This sample alert will send an email when TEMPDB database gets larger than 0 KB. This setting is set zero so the alert can be tested. Once verified, you will need to update its settings to a reasonable amount or disable it.

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 Response in left pane

  • Checkmark > Notify operators
  • Checkmark > E-mail for the operator

Click Options in left pane

  • Checkmark > Include alert error text in E-mail
  • Delay between response: 2 minutes

Click OK

Verify Alert is Working

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

Open the Alert. Click History in the left pane.

The fields are updated when the alert is triggered.

Stop the 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 alert

  • Click General in left pane and remove the checkmark from Enable and click OK
  • OR
  • Right click on Alert name and select Disable

To change the settings

  • Click General in left pane
  • Change value to value larger than existing TEMPDB database file save. The current file size is included in the e-mailed alert.
  • Click Options in left pane
  • Change response to 720 minutes which is 12 hours
  • Click OK

Troubleshooting

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 Manager Studio, you can script the alert so you can easily create it in other instances. See this tip on how to create scripts.


Last Update: 6/19/2008


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.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, December 18, 2014 - 10:19:36 AM - Jigglypuff Read The Tip

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 Read The Tip

 David Bird, 

Thanks, well explained, very useful


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

 

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


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

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

 


Monday, May 26, 2014 - 3:12:03 AM - Munnan Read The Tip

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 Read The Tip

Thank you David. Excellent and still relevent in 2014! Cheers! :)


Tuesday, November 12, 2013 - 6:32:11 PM - Luis Vega Read The Tip

Sr thank you very much!.


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

 

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 Read The Tip

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 Read The Tip

Thanks a lot sir. 


Wednesday, May 15, 2013 - 10:47:12 AM - Paul Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Thank you David. Excellent guide and still relevent in 2012! Cheers! :)


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

@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 Read The Tip

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 Read The Tip

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 Read The Tip

http://www.mssqltips.com/tip.asp?tip=1803


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

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

 Steve




 
Sponsor Information