Still cant get DB Mail Up, but Job Notifications Work?

Question

Sql 2008 R2

OK first off I might be confused. When you enable DB mail and go through all the steps, is that the same thing as if you right click on a job|properties|notifications| and choose actions to perform when job completes Email etc etc. That part works fine. I was under the assumption that's different and I don't need that selected if I do a Notify Operator on the Maintenance Plan itself. Here is what I have done.

Enabled DB Mail. Setup Account and Profile. I click send test mail and I get that just fine. Profile is public and defaulted.

Setup Operators with name and email.

Maintenance Plan-modify-General-Notify Operator Task. (one for success and one for failure) attached to main db backup

SQL Srv Agent-properties-alert system- enable mail profile set to Database Mail and chose profile prev created and restarted it.

Firewall turned off

When I run the maintenance plan the backup occurs but the attached Notify Operator Success does not.  Test email from DB Mail setup works fine though.

What am I missing?

Answer 1

check db mail log for details of mail not coming. Error will help you in understanding the thigns in details.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

I ran the following against msdb

SELECT * FROM msdb.dbo.sysmail_event_log;
The only thing it shows is a bunch of Database mail is started and then then shutting down. I dont see any errors.

09/21/2012 21:03:14,,Information,16,DatabaseMail process is started,4336,,,9/21/2012 9:03:14 PM,09/21/2012 08:37:46,,Information,14,DatabaseMail process is shutting down,1756,,,9/21/2012 8:37:46 AM,
09/21/2012 08:20:57,,Information,13,DatabaseMail process is started,1756,,,9/21/2012 8:20:57 AM

etc etc

I try the Send test email and I get that instantly. I ran the backup job again, and no email. Where or how else would I look for an error?

Answer 3

I also found this:

To send Database Mail, users must be a member of the DatabaseMailUserRole. Members of the sysadmin fixed server role and msdb db_owner role are automatically members of the DatabaseMailUserRole role. To list all other members of the DatabaseMailUserRole execute the following statement:

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole

When I run that command it comes up blank. So do I need to add an account using the
sp_addrolemember @rolename = 'DatabaseMailUserRole'
   ,@membername = '<database user>';

What would be the user that I add if so? My account or whatever account launces the backup job? Kinda lost on this one. Or is there another way?

Answer 4

It seems you user going to run backup job is not having proper rights.

Check the access rights for SQL agent service account & share the same.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 5

Thanks, I will try and check that today after a few meetings I have.

Answer 6

Still cant get it. I excuted the following to see what users had access
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;

It came up empty. So I ran the following against my maintenance plans to get the owner:
select * from msdb.dbo.sysssisp

Then I got the name that belonged to that SID and changed the owner of the maintenance plan to match the owner of all the other maintenance plans. After that I went to the account that was the owner of the maintenance plan\properties\user mapping and checked msdb and role DatabaseMailUserRole

I still have the same issue. If I go to Database Mail and do a test email I get it fine. If I execute the Maintenance Plan, the attached Notify Operator does not work and send the email.

Answer 7

Hi Carito,

According to your description, I think it is not a db mail sent issue.

Please first check the job history for your maintenance plan, in SQL Server Agent, right click your job and click “view history” (every maintenance plan will have a related job in Agent). Is there any failed message?

The most possible reason for this issue is that your db mail profile is not configured well. The Notify Task would send the mail by default profile, but your default profile is not configured.

Please Follow the below steps as below:

1. Go to Management --> Database Mail

2. Right Click then Configure Database mail, you will wizard then click next

3. select Manage Profile security -- next

4. Beside Profile name by default there will be no , Please make it as yes

5. Finish

Try to execute the maintenance plan again.

By the way, you can configure the notification in job properties instead of adding a notify task in maintenance plan. If you want the both failed and success mail, you can choose “when the job completes”.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 8

Thanks

I checked the job history log and there are no errors. It shows as hacing executed and the backup is there if I verify.

The Database Mail part I already checked and the profile name is set as is the defualt option to yes.

If I go to the job itself though and go to Notifications and turn that on and enter in the email and run the Maintenance Plan I will get the email from the job, I will not get an email setup through the Maintenance plan. It also appeats that the user that runs the job (a dmoain user) is not the same user that own the Maintenance Plan.

I realize I could just use the job notification and remove the added Notify Operator boxes I added to the Maintenance Plan itself, but it just bothers me that it doesnt work "that way"

Answer 9

Carito,

Your maintenance plan can be executed successfully, am I right?

Run: select * from msdb.dbo.sysmail_allitems

Is there a corresponding record in the view after you executed the plan? If yes, what's the "sent_status"? According to the situation you posted, it should be "sent".

So, the problem should occured on operator. Please ensure the operator you choosed is the same as you configured in job properties, and the email address is not have typo.

If all above has no problem I would like suggest you to check if your operator email has blocked the outgoing email address.

At last, I want to inform that the Notify Operator Task is also sending database mail to operator as job notification. It uses the sp_notify_operator procedure. Try to run this SP directly following this article see what happens:

http://msdn.microsoft.com/en-us/library/ms188349.aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 10

Thanks Eric. Id never ran that command before. So it appears that if i right click on the plan and do an execute now. I DO NOT see a corresponding a entry in that table. However if I right click on Database Mail and do a test email, then run the command I do get an entry

14 2 USERID NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on SERVERA. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256   0 0 2012-09-27 08:07:51.303 DOMAIN\myuserid 2 sent 2012-09-27 08:07:51.000 2012-09-27 08:07:52.020 sa

Looks to me something like an account issue? It sent that using my account ID but the maintenance itself is not owned by me. Its owned by another local account. I already added the owner of the maintenance to the DatabaseMailUserRole by doing the following unless I did that wrong:

Right clicked on SQL account under Security\Logins then Properties\User Mapping. I checked the box for the msdb database and put the user account name in the User column and left the Defualt Schema set to dbo. Is that correct or should both columns say dbo? Then in the Database role membership for msdb I selected the box DatabaseMailUserRole and Public.

When I run the command: EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
that user account now shows as a member of the DatabaseMailUserRole.

OK I also ra n the following:
USE msdb ;
GO
EXEC dbo.sp_notify_operator
@profile_name = N'Live Alert Email',
@name = N'User Name',
@subject = N'Test Notification',
@body= N'This is a test of notification via e-mail.' ;
GO

I got a mail queued result and did ge the email. Go back to the plan, run it and dont get the email though.

Answer 11

Carito,

If it is permission issue, your maintenance plan cannot be executed successfully.

Your scenario is: maintenance plan executed successfully and the maintenance plan has notify operator task. But after executed, the database mail is not be sent. Since the select * from msdb.dbo.sysmail_allitems has no new entry after you run the maintenance plan, we can determine that the notify operator task has not been started.

One reason for that, is the notify task is not been enabled. Please right click the notify operator task in maintenance plan design wizard, is the "enable" button there? The disabled task will be in light pink color.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 12

Both the success and failure notify operators in the Maintenance Plan Design Wizard are enabled. When I right click I see disabled.