?
Solved

NOTE: Failed to notify 'operator' via email.

Posted on 2010-01-12
13
Medium Priority
?
1,628 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.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 70

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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