Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

NOTE: Failed to notify 'operator' via email.

Posted on 2010-01-12
13
Medium Priority
?
1,694 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:Jim P.
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26293616
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
 
LVL 38

Author Comment

by:Jim P.
ID: 26293723
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26298545
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Author Comment

by:Jim P.
ID: 26304385
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
 
LVL 72

Expert Comment

by:Qlemo
ID: 26304758
What I didn't get yet - is there a workaround? Anything you can do that email is sent again?
0
 
LVL 38

Author Comment

by:Jim P.
ID: 26306432
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1000 total points
ID: 26310687
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
 
LVL 38

Author Comment

by:Jim P.
ID: 26355620
I'm working off-site this week, so I'll have to wait until next week to check it.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 26574412
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26579618
>> 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
 
LVL 38

Author Comment

by:Jim P.
ID: 26582555
>> 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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 26598790
Ok.. Applying SP3 would help..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question