Email notification does not send 2

[sqlcasey]

Hello,

re: SQL Server 2005, SP1

I set up Database Mail on a clustered server using the db mail wizard in Management Studio. I set up an operator using a mail group. The test email worked fine. I also restarted the SQL Server Agent on node 1 of the cluster (twice).

Next, I set up a notification on a simple backup job to send an email to the operator when the job completes.

However, the email is never sent out when the job completes. I have checked the operator history and there is no history of an email ever being sent. I also checked the current log and there are no error messages whatsover regarding the mail not being sent. I also checked the application log on node 1, and there is no error there either.

I have run the backup job several times now, sent myself another test email from the server (which works fine), but for some reason the email just won't send on job completion.

Has anyone had this problem and how to resolve it? I though restarting the Agent would solve the problem, but it has not.

Thanks

 

[ptheriault]

Casey,
I haven't seen that problem before but I did find this link. Maybe it will help you.

http://msdn2.microsoft.com/en-us/library/ms189635.aspx

- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

At this point, I am wondering if there is some kind of bug, or obscure setting that is preventing the mail from being sent from the *job*, not the server itself. The server itself sends emails no problem.

I set up notifications on another server on the same network last week, and had problems with the job sending the notification until I restarted the Agent. Once I restarted the Agent, the job started sending the email.

However, this is a clustered server, so I'm wondering if that has something to do with it.

Whatever it is, it seems obscure.

 

[ptheriault]

I send e-mails on job failures from my cluster all the time. I didn't have to do anything different to configure it.
Have you tried to configure an alert based on the job failure and send an email from the alert? It just dosn't make sense that you can send a test email and not one from the job. By sending the test e-mail I know that all your permissions and paths are correct. Is the job really exiting with a status of failure? Check the job properties to see what status it completes with if there is an error.

- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

I have tested it both, 1.) on completion, 2.) on success

As I mentioned, the test emails send fine

Our sysadmin here says that no special permissions needed, since it's only sending.

What's even stranger is that there are no error messages whatsoever in any logs indicating a problem. It just doesn't send from the job.

I think it is some kind of bug or very obscure setting I am missing, as I have email notifications just fine on 3 other different servers.

 

[ptheriault]

Do you see any records in the msdb.dbo.sysmail_mailitems table? There should be a record in that table for each sent mail item.


- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

The only thing in there are the test emails I sent this morning

 

[ptheriault]

That suggests that the job isn't sending it's info to db mail.
What is you created an alert to fire when a job fails. Have that alert send the email. does that work?

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Hmmm. You know, I just changed my operator on a job in 2k5 to "When Job Completes" instead of "When Job Fails" and tested a backup job. Same thing happens to me. No email. And I know DBMail is working because we've used it for other things and the Operator address is a valid email addy we use every day...

When I check the Operator properties, it says "never emailed" on the history tab.

So, I tested it with "On Success" and got the same result. NADA. Nichts. Nothing.

Okay, now I'm on a mission to find the answer to this problem myself....



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

Catadmin,

Cool... I know I am not crazy... like I said, I have db mail WORKING from job notifications on other servers here.

And as I also mentioned, restarting the SQL Server Agent got rid of this problem on those other servers. According to posters on other boards, this solved their problem too, it's a kind of buggy thing.

However, on the clustered server set up, restarting the agent had no effect.

 

[Catadmin]

Hey, were you working with domain group emails or individual emails?

One of my co-workers mentioned that he worked on this issue earlier and thinks it has to do with the operator using a domain group email instead of a list of individual email addresses. That this isn't a cluster bug but a SQL 2k5 issue.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Okay, I was searching around BOL and found the following two things:

1) SQL Server Agent has to be configured to use DB Mail or SQL Mail.

2) A fail-safe operator has to be set up.

Both can be done by right-clicking SQL Server Agent and going to properties. Navigate to the Alert System tab. The Email configuration is the one on top and Fail-Safe Operator is about 2/3rds of the way down.

After that, you have to restart the SQL Server Agent to get the email notification to work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Okay, I'm recanting part of what I just posted. The Fail-Safe Operator doesn't appear to be necessary, just recommended for when all other notifications fail.

Sorry about the confusion. But, I did just test this and it works on all scenarios: Completed, Success and Failure.

WHOOHOO!





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

So wait, how did you get it to work?

I did the following:

1.) changed email to myself only
2.) restarted Agent

NADA ZIP NOTHING

Still does not work

 

[ptheriault]

I just followed these steps for one of my 2005 server that was having problems and it worked.

1) SQL Server Agent has to be configured to use DB Mail or SQL Mail.

2) A fail-safe operator has to be set up.

Both can be done by right-clicking SQL Server Agent and going to properties. Navigate to the Alert System tab. The Email configuration is the one on top and Fail-Safe Operator is about 2/3rds of the way down.

After that, you have to restart the SQL Server Agent to get the email notification to work.

Nice find cat.

- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

Hi Paul,

No I'm sorry it didn't work. Everything is configured correctly:

1.) DB Mail set up
2.) Operator and fail-safe operator set up
3.) Agent restarted
4.) Notification to send on job completion set up

It just does not send

Test emails work fine

 

[ptheriault]

Do you get an error in the job history that says the job was unable to send email? I was getting that in my job, but by following Cat's steps it corrected it. Is the email address that you send the test email to the same address that you are trying to send the job completion email too? Have you check the outgoing mail folder on the SMTP server? Is it possible the error is with the email address?

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

You need to make sure that SQL Server Agent is set up to DBMail. This is NOT the same thing as configuring DBMail. Both are separate steps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

You need to make sure that SQL Server Agent is set up to DBMail. This is NOT the same thing as configuring DBMail. Both are separate steps.

Not sure what you mean by this?

 

[Catadmin]

Right click SQL Server Agent. Go to the Properties -> Alert System tab. Is "Enable Mail Profile" checked? If so, is DatabaseMail chosen under Mail System?

Configuring Database Mail is done by going to Management -> Database Mail and right clicking that. This is how you initially configure DBMail. Once that is done, you configure your operator. I assume this is the process you've already followed since you say you've configured DBMail already.

After configuring Database Mail, then you go to SQL Server Agent, as I said above, and enable DBMail there. Then restart SQL Server Agent and you should be good to go.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"