Database mail not sending messages in queue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database mail not sending messages in queue

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-01-20 : 16:25:00

Hello, i have had database mail running for a while now, and for some reason every now and again it'll just stop sending messages. it's doing it now and i'm not in a position to be able to reboot the server to get it working again.

i've tried: exec sysmail_start_sp no dice, i've restarted the agent,

if i run: exec sysmail_help_queue_sp @queue_type = 'Mail'


mail	43	NOTIFIED	2009-01-19 19:20:19.937	2009-01-19 20:56:02.717

any advice?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 16:52:26

Hello

Well if your using SQL Management Studio then you can check the mail log. That should give you a good start !

Management --> DatabaseMail --> ViewLog

r&r

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-01-20 : 16:56:35

i see that yesterday about the time the problem started again, i got this in the log:

Date 1/19/2009 1:56:04 PM
Log Database Mail (Database Mail Log)

Log ID 532
Process ID 2456
Last Modified 1/19/2009 1:56:04 PM
Last Modified By NT AUTHORITY\SYSTEM

Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ValidateConnectionAndTransaction()
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)

so now how to i get it to start sending messages again? as i said, i usually just reboot the server, but i cant right now because i'm building some indexes :(

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-01-20 : 17:37:24

got it working!!!

just started / stopped the mail with:

exec sysmail_stop_sp
exec sysmail_start_sp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources