October 26, 2009 at 11:35 am
Database mail will run fine for hours then stops sending with the error listed below. Mail still gets queued but not sent. When I run dbo.sysmail.stop.sp the query never finishes. I have to use task-manager to kill databasemail90 to get it to end. I then have to start/stop/start to get mail running again. Database mail ran fine for several months then started doing this a couple of times a day. Part of the log is listed below. Any suggestions what to look for?
10/23/2009 17:47:22,,Error,110251,1) Exception Information<nl/>===================<nl/>Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException<nl/>Message: The Transaction not longer valid.<nl/>Data: System.Collections.ListDictionaryInternal<nl/>TargetSite: Void ValidateConnectionAndTransaction()<nl/>HelpLink: NULL<nl/>Source: DatabaseMailEngine<nl/><nl/>StackTrace Information<nl/>===================<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()<nl/> at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()<nl/> at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da<c/> Int32 lifetimeMinimumSec)<nl/> at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName<c/> String dbServerName<c/> Int32 lifetimeMinimumSec<c/> LogLevel loggingLevel),17884,,,10/23/2009 5:47:22 PM,NT AUTHORITY\SYSTEM
10/23/2009 17:39:42,,Error,110250,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-10-23T17:38:02). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
Sending Mail using Account 2 (2009-10-23T17:39:42). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
),17884,118631,,10/23/2009 5:39:42 PM,sa
10/23/2009 17:35:22,,Error,110249,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-10-23T17:33:42). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
Sending Mail using Account 2 (2009-10-23T17:35:22). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
),17884,118631,,10/23/2009 5:35:22 PM,sa
10/23/2009 17:34:42,,Error,110248,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-10-23T17:33:02). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
Sending Mail using Account 2 (2009-10-23T17:34:42). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
),17884,118629,,10/23/2009 5:34:42 PM,sa
10/23/2009 17:30:22,,Error,110247,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-10-23T17:28:42). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
Sending Mail using Account 2 (2009-10-23T17:30:22). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
),17884,118628,,10/23/2009 5:30:22 PM,sa
10/23/2009 17:30:22,,Error,110246,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-10-23T17:28:42). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
Sending Mail using Account 2 (2009-10-23T17:30:22). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
),17884,118629,,10/23/2009 5:30:22 PM,sa
10/23/2009 17:27:02,,Information,110245,DatabaseMail process is started,17884,,,10/23/2009 5:27:02 PM,NT AUTHORITY\SYSTEM
10/23/2009 17:25:02,,Information,110244,DatabaseMail process is shutting down,16964,,,10/23/2009 5:25:02 PM,NT AUTHORITY\SYSTEM
10/23/2009 17:14:02,,Information,110243,DatabaseMail process is started,16964,,,10/23/2009 5:14:02 PM,NT AUTHORITY\SYSTEM
10/23/2009 17:08:50,,Information,110242,DatabaseMail process is shutting down,16588,,,10/23/2009 5:08:50 PM,NT AUTHORITY\SYSTEM
10/23/2009 16:58:50,,Warning,110241,Cannot send mails to mail server. (The operation has timed out.),16588,118612,1,10/23/2009 4:58:50 PM,NT AUTHORITY\SYSTEM
10/23/2009 16:58:43,,Warning,110240,Cannot send mails to mail server. (The operation has timed out.),16588,118621,1,10/23/2009 4:58:43 PM,NT AUTHORITY\SYSTEM
10/23/2009 16:58:43,,Warning,110239,Cannot send mails to mail server. (The operation has timed out.),16588,118620,1,10/23/2009 4:58:43 PM,NT AUTHORITY\SYSTEM
10/23/2009 16:58:43,,Warning,110238,Cannot send mails to mail server. (Failure sending mail.),16588,118619,1,10/23/2009 4:58:43 PM,NT AUTHORITY\SYSTEM
October 26, 2009 at 12:12 pm
First question,
What has changed?
Did you do a CU or service pack either SQL or Windows?
Is Anti-virus on the server? Did it get an update?
Did anything on the mail server change?
Did anything on the network change?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2009 at 1:12 pm
Jack,
I wish something had changed so I'd know where to start. But I don't think anything has. Symantec antivirus has always run realtime only, no full scans, and nothing shows in the history logs. Automatic updates on the server is turned off. The only change I've made was done after this problem started. I added a second account to the profile. Since then I can tell that the sometimes the first account will stop, the second account will send a few, then the first account will send again.
Is there any significant part of the error message that means anything meaningful? It looked pretty generic and I haven't been able to find anything relevant during my research.
G
October 26, 2009 at 1:35 pm
G,
I don't see anything in the error message that really helps me. I know when I have had issues it has usually been caused by AV. Does the problem occur during the same time period every day?
You may want to run a server-side trace on msdb to see if there are issues there. Perhaps some kind of blocking or deadlocking?
Even if auto update is turned off someone could have manually done a Server or SQL update. In my experience, rarely does an issue just appear. Usually there has to have been a change somewhere.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2009 at 1:45 pm
Jack,
Could you explain more on the msdb trace?
G
October 26, 2009 at 9:56 pm
Well, I would use SQL Server Profiler to setup the trace. I'd filter on the database name (msdb) and include events that have to with locking/blocking and the RPC:Starting and RPC:Completed events and T-SQL:BatchStarting and T-SQL:BatchCompleted events. Then script it out so I could do a server-side script (less impact on the system than monitoring with the Profiler GUI). I'd add a stop time to the trace so it only run for a couple of hours around the time I anticipate the problem. Look up server-side trace in BOL for details.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply