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?
Note: This solution involves restarting SQL Agent.
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.
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
Restart SQL Agent to activate settings.
Warning: Restarting SQL Agent will cancel any executing jobs.
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
Counter: Data File(s) (KB)
Alert if counter: rises above
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
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
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
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.
Modify the alert to execute a job that captures all current connections using sp_who2 .
Create alerts for other criteria you wished to monitor.
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