NOTE: Failed to notify 'operator' via email.

I'm now the DBA in charge of a raft of SQL Server 2005 Standard edition SP2 running on Win2k3 SP2. They are running on multiple separate domains (we are hosting for other companies) using the SMTP to go to the same Exchange Server. I'm using Basic Authentication to authenticate to the the mail server.

I'm running into an issue where we setup notifications to go out for the nightly full backups (as well as other stuff). They will work for several days to weeks, then we start getting a nice little message on the the backups.
--------------------------------------------------------------
The job succeeded.  The Job was invoked by Schedule 22 ( Maintenance Plan.DB Full Backup).  The last step to run was step 1 (Orcas DB Full Backup).  NOTE: Failed to notify 'OperatorName' via email.
--------------------------------------------------------------

We can't go to SP3 because of application constraints. It is really starting to tick me off that it will will for a long time and then fail.

I've upped the  AccountRetryAttempts to 3 and upped the DatabaseMailExeMinimumLifeTime to 1200 seconds. I even have a Start Mail task (code snip below) before the backups begin.  The backups only take minutes.

Anyone have any ideas?

-------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
      Mar 23 2007 16:28:52
      Copyright (c) 1988-2005 Microsoft Corporation
      Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

/*********************************************************************/
/* Check the mail engine. Start the service if is is stopped         */
/* -- the DB Mail engine shuts down after 10 minutes (of inactivity) */
/* -- this setting can be changed, but we probably don't need to.    */
/*********************************************************************/

DECLARE @rc INT

IF NOT EXISTS (SELECT * FROM msdb.sys.service_queues
               WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
BEGIN
    EXEC @rc = msdb.dbo.sysmail_start_sp
END

Open in new window

LVL 38
Jim P.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Just enable DatabaseMail in SQL Server Agent via

* Right click SQL Server Agent
* Choose Properties
* Alert System
* Check the checkbox
* Restart SQL Server Agent Service

Try testing it out once..
0
Jim P.Author Commented:
It is enabled. The issue is that after a few days or weeks -- it starts failing. I go in, and turn off the DB Mail in the agent, restart the agent, then turn on DB Mail back on, restart the agent again and we start working again. Then a week later I'm getting the same message again.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Kindly check the Service account set for SQL Server Agent Service..
Any kind of password expires or some other related things can disturb this too..
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jim P.Author Commented:
None of the users for the services have a password expiration set.

And what is even worse, it seems random on which servers will crap out on any given day.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What I didn't get yet - is there a workaround? Anything you can do that email is sent again?
0
Jim P.Author Commented:
Generally if I turn off the e-mail in the SQL agent, restart, turn it back on, restart the agent again it will work the next night. Sometimes I have to totally take it all out and add it back in again, including deleting the operator.

And then sometimes it works the next day without me doing a thing.
0
Eugene ZCommented:
check the solution from
Failed to notify ''operator name'' via email?
http://www.sqlservercentral.com/Forums/Topic253575-149-1.aspx

check this one

microsoft.public.sqlserver.server with the topic "How to setup Operator for sending alerts?

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic5023.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Author Commented:
I'm working off-site this week, so I'll have to wait until next week to check it.
0
Jim P.Author Commented:
I can't get it consistent among all servers. So I have to just go back and play every couple of days to weeks. :-( Very aggravating. Maybe when we can finally go to SP3 the issue will resolve itself.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Maybe when we can finally go to SP3 the issue will resolve itself.

But you have mentioned in your question that you can't apply SP3 because of application constraints.
Kindly confirm

PS: Its recommended for Microsoft Products to stay up to date with the latest Service packs..
0
Jim P.Author Commented:
>> But you have mentioned in your question that you can't apply SP3
>> because of application constraints.

The next version of of our app is scheduled for late summer/early fall.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ok.. Applying SP3 would help..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.