asked on

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

Raja Jegan R

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..

Jim P.

ASKER

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.

Raja Jegan R

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..

Jim P.

ASKER

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.

Qlemo

What I didn't get yet - is there a workaround? Anything you can do that email is sent again?

Jim P.

ASKER

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.

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim P.

ASKER

I'm working off-site this week, so I'll have to wait until next week to check it.

Jim P.

ASKER

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.

Raja Jegan R

>> 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..

Jim P.

ASKER

>> 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.

SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial