5

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.

Vinesh Senthilvel
  • 313
  • 1
  • 6
  • 15

1 Answers1

2

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.

LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
Divyesh Gohil
  • 21
  • 4