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