DatabaseMail process is shutting down

Question

I Have configured the Database Mail in INSTANCE1 and INSTANCE2. Whenever i try to send a mail using the following code, i get output as "Mail queued" in INSTANCE1.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Vinesh',
@recipients = 'VineshSenthilvel@gmail.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message - Default' ;

But the mail is not delivered to my mailbox. And i tried executing the above query in INSTANCE2, the mail got delivered. I have verified that Database Mail configuration seems to be perfect in both the instances.

I can able to find the Database mail process is shutting down in the Database Mail Log. Also found an error mentioned below.

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, Byte[] encryptionKey, Int32 connectionTimeout)

Is there is any solution for this? Thanks in Advance.

Answer 1

I have had the same issue and I followed these steps to resolve that.

1. EXEC msdb.dbo.sysmail_help_queue_sp and check if length > 0 then DELETE QUEUE items.

2. Use the following query to delete queue items:

   DECLARE @GETDATE datetime
   SET @GETDATE = GETDATE()
   EXECUTE msdb.dbo.sysmail_delete_mailitems_sp 
   @sent_before = @GETDATE;
   
   GO
   

3. EXEC msdb.dbo.sysmail_start_sp, start the process and check.