Here's a wierd one - Notify Operator only works some of the time

Question

Here's a wierd one - Notify Operator only works some of the time

Moffitar

Ten Centuries

Points: 1185
More actions
May 25, 2010 at 12:34 am

#387005

Hi All,
I've been working with Database Mail for some time but I haven't seen this one before. I have a maintenance plan that does the following:
1. Check database integrity
On Success:
2. Perform backups
On Success:
3. Perform a maintenance cleanup
On Success:
4. Notify operator of success
Steps 1 - 3 are also linked (via On Failure arrows) to a singular Notify Operator of Failure task.
The maintenance plan does exactly what I want it to, and if everything goes correctly, it successfully sends an email to an operator.
If steps 1 - 3 fail, the job ends in an error state, but does not trigger the Notify Operator of Failure task.
I was able to recreate the problem by creating another maintenance plan on the same instance with the same steps. Its "Notify Operator" on failure task also doesn't work.
It occurs to me that maybe I'm missing something, so here's some of the details of my SQL server:
SQL 2008 + SP1 (10.0.2531) x64 on Windows 2008 R2
DB Mail profile is public and default
I do have a slightly unusual profile, in that it uses two accounts:
1. A connection to a SharePoint SMTP service (where it catches emails directed at document libraries)
2. A connection to a UNIX-based smtp server (which routes mail to regular mailboxes and my SQL DBA mailing list)
The Profile will attempt to send to the SharePoint server first. The SharePoint server does not relay. If the document library email address doesn't exist, SQL will raise a warning (in the Database Mail log) and the profile will use the second account on the list, which is a real mail server and can relay the message to any mailbox.
It works really well, actually. When the maintenance plan completes successfully, the message is sent to the drop folder on the SharePoint server, and SharePoint routes the email to the correct library, and we have a central archive of all DB Mail notifications.
But if the job fails (for example, if the backup disk is out of space), none of this happens. According to the log, the job doesn't even try to send a notification. Looking at the DBMail log, the Mail service does not start. No email is delivered to the drop folder of the receiving SMTP server. So I don't think my Database Mail configuration is the problem here. It is apparently something to do with the way the job itself handles errors.
So, am I missing something obvious here?
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Bueyes SSC Eights! Points: 871 More actions · Answer 1

I think that you need try to send a notification to Windows Application Event Log when the job fails, this can help to identify where the job fails, because maybe you are using the mail to send notification when job success, so you have an additional point to check what happen with the job. I hope this can help you.

Moffitar Ten Centuries Points: 1185 More actions · Answer 2

Update:

I can run the job manually and it succeeds. All steps execute successfully, and the notification is sent. I can also break the job (by putting in an incorrect path) and it will fail, but the notification is not sent. Operators are enabled (it is the same operator in either case).

In the job log --

If the job succeeds: the "Notify Operator" step is marked as successful

if the job fails: the "Notify Operator" step is OMITTED. As I said, it looks like it doesn't even try to notify.

Moffitar Ten Centuries Points: 1185 More actions · Answer 3

Update:

This appears to be happening on all my SQL 2008 instances.

I played around with one of my jobs and replaced the On-Failure "Notify Operator" task with a simple "Execute T-SQL Statement" task.

I forced the Maintenance plan to fail, and the log showed the expected errors for the backup database task... and a successful T-SQL task.

So the On-Failure task was being triggered after all. It just doesn't seem to want to perform the "Notify Operator" task.

Moffitar Ten Centuries Points: 1185 More actions · Answer 4

Update:

Using the "Execute T-SQL Statement" task, I am able to manually send an email when the job fails:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SharePoint Profile',

@recipients = 'myemail@mydomain.com',

@body = 'The job has failed.',

@subject = 'Test Message' ;

So it's not the mail system that is failing.

Moffitar Ten Centuries Points: 1185 More actions · Answer 5

I also went to the Job properties for Notifications and told it to send a notification via email if the job failed. The notification was never sent.

Moffitar Ten Centuries Points: 1185 More actions · Answer 6

OK, I think I tracked it down.

I went to the SQL Agent properties in Management Studio and, under the "Alert System" tab, I checked "Enable Mail Profile", accepting the default profile. Also, just to make sure, I enabled the Fail-safe Operator, accepting the default operator as well, to be notified by email.

My test worked! The Notify Operator task is now triggering on failure!

So: lesson learned. In the default configuration of SQL Server, email notifications work, but only sort of. In order to get full functionality, you have to enable the mail profile for the SQL Agent. This is not a default setting.

I was misled because when I went to set up Database Mail on my SQL 2008 server, every indication was that mail was working perfectly. And the Notify Operator tasks were working, but only when the Maintenance Plans completed successfully.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 7

This is actually a rather simple issue to fix - but not intuitive. For the failure notify operator task, you probably have solid red lines connecting from each of the tasks to this one task. Essentially, what this is telling the system is that this task will only be executed if ALL of the predecessor tasks fail.

Because the other tasks have completed successfully, or not been run because they only run on success of the previous task - the notify operator task does not fire.

To fix this, right-click on any one of the dependencies and change the AND criteria to an OR criteria (bottom of dialog box). This will change the solid red lines to dashed (dotted) red lines which states that the task will fire if ANY of the predecessor tasks fail.

That should be it...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

andrew.hopkinson Valued Member Points: 52 More actions · Answer 8

thank you for this! It seems to be that this was not necessary in pre R2 installations of SQL Server 2008. BTW, I also had to restart my SQL Server Agent for it to work.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 9

andrew.hopkinson (11/22/2010)
thank you for this! It seems to be that this was not necessary in pre R2 installations of SQL Server 2008. BTW, I also had to restart my SQL Server Agent for it to work.

Yes - it was required in previous versions. However, there was a fix from one of the service packs or CU's that allowed this to work without configuring SQL Server Agent.

So - in SQL Server 2005 you had to configure this, but I think it was after SP2 and one of the CU's where you no longer had to do this. Then, in 2008 it came back...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

andrew.hopkinson Valued Member Points: 52 More actions · Answer 10

andrew.hopkinson

Valued Member

Points: 52

November 22, 2010 at 12:54 pm

#1253978

Okay, I never did a 2005 install. Thanks!

Z. Yin Newbie Points: 3 More actions · Answer 11

all by luck I found this article. good to know.

thanks