Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Mail not working on job completion Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 8:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:47 AM
Points: 71, Visits: 154
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.
Post #1547820
Posted Wednesday, March 5, 2014 8:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1547830
Posted Wednesday, March 5, 2014 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1547842
Posted Wednesday, March 5, 2014 9:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:47 AM
Points: 71, Visits: 154
Hello --

Thanks for your reply. I checked the SQL Agent Error Logs, and I came across the following entries:

Date 3/4/2014 1:29:25 PM
Log SQL 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)


Date 3/4/2014 10:00:04 PM
Log SQL 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?






Post #1547888
Posted Wednesday, March 5, 2014 9:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1547892
Posted Thursday, March 6, 2014 6:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:47 AM
Points: 71, Visits: 154
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.
Post #1548261
Posted Thursday, March 6, 2014 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:47 AM
Points: 71, Visits: 154
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.
Post #1548278
Posted Thursday, March 6, 2014 10:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1548388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse