March 5, 2014 at 8:08 am
Hello --
We have SQL Server 2008R2 64-bit running on a Windows 2012 Standard 64-bit distribution. I have set up a maintenance plan with three backups, and I configured Database Mail to send notifications upon the completion of the Full and Differential Backups, and notifications on the failure of the Transaction Log backups. The problem that I am encountering is Database Mail is not sending out any notifications.
The procedure that I am outlining has been successfully implemented on several other database servers. The only difference being the current server runs the Windows 2012 64-bit operating system, while the other servers run the Windows 2008R2 64-bit operating system.
I set up an e-mail account that has access to the SMTP server of our company. The SMTP authentication was set to "Windows Authentication using Database Engine service credentials". A test e-mail was successfully sent to an address that has been set up for the several administrators in charge of the database server.
I then went to the SQL Agent, and created an operator with the destination e-mail address. Once that was done, I went to the jobs in question, and through the Notifications tab, I clicked on the E-Mail entry, and provided the appropriate information in the accompanying fields. I made sure the option "When the job completes" was selected for the Full and Differential backups, while "When the job fails" was selected for the Transaction Log backup.
I then went to the SQL Agent icon, and under Properties, I enabled the Mail Profile, as well as the Fail-Safe operator. Also, I made sure the global public profile was active, and set to default.
Finally, I restarted the SQL Agent to have the changes that I described go into effect.
What other step(s) do I need to take in order to correct this problem?
Thanks.
March 5, 2014 at 8:19 am
First thing is to look at the SQL Agent History (not the Job History) to see what it says. You can find it in SSMS under the SQL Agent\Error Logs folder. It should have information about what notifications were sent or failed to send.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 5, 2014 at 8:31 am
Use the query below to get a list of all mails sent from the instance during the last week. Take a look at the description column to see for any (failure) descriptions.
-- show a list of all mail-items
SELECT
sysmail_allitems.mailitem_id
, sent_status
, recipients
, subject, body
, send_request_date
, send_request_user
, sent_date
, sysmail_allitems.last_mod_date
, sysmail_event_log.event_type
, sysmail_event_log.description
FROM msdb.dbo.sysmail_allitems
LEFT OUTER JOIN msdb.dbo.sysmail_event_log
ON sysmail_allitems.mailitem_id = sysmail_event_log.mailitem_id
where send_request_date > dateadd(dd, -7, getdate())
--and sent_status = 'failed'
order by
send_request_date desc
March 5, 2014 at 9:31 am
Hello --
Thanks for your reply. I checked the SQL Agent Error Logs, and I came across the following entries:
Date3/4/2014 1:29:25 PM
LogSQL Server Agent (Archive #1 - 3/5/2014 8:57:00 AM)
Message
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStre)
Date3/4/2014 10:00:04 PM
LogSQL Server Agent (Archive #1 - 3/5/2014 8:57:00 AM)
Message
[264] An attempt was made to send an email when no email session has been established
Is the profile name mentioned in the error message referring to that which had been set up during the initial configuration?
March 5, 2014 at 9:37 am
Yes, the error points to a misspelled or removed profile name. Check the profile settings again in the database mail configuration and the SQL Agent properties.
If the problem persists you could also try the following workaround:
Change the mail setting in the SQL Agent properties from Databasemail to SQLMail and back again (with restart SQL Agent service after each change). I know some (previous?) versions had some kind of bug regarding this setting.
March 6, 2014 at 6:59 am
Hello --
I believe I found the cause of the problem. When I checked the profile within the wizard, I noticed there were two entries. One was the correct one, the other was one based on the e-mail account of the intended recipients. I removed this latter entry, and restarted the SQL Agent.
I checked the notifications this morning, and the e-mail had been sent.
Thanks for the help.
March 6, 2014 at 7:26 am
Hi --
I had one follow-up question. If I wanted to change the subject, and the job run line within the e-mail notification, can I simply rename the backup plan, and/or the individual jobs within the plan, or do I need to create everything from scratch?
Thanks.
March 6, 2014 at 10:27 am
You could probably rename a job, but when you alter a maintenance plan all jobs will be deleted and re-created from scratch. Your changes will be undone.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply