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.

5
  • If the instances are on different servers, make sure you have .Net 3.5 installed on the one where it isn't working. Sep 21 '16 at 14:18
  • Check the mail profile on INSTANCE1 and ensure A.) It's not set to private B.) if it is private, do you have access to see it Sep 21 '16 at 14:24
  • Both the instances are in same server and .Net 3.5 is installed in it. Checked that mail profile is set to public. Sep 21 '16 at 15:36
  • Another thing to check is to make sure the mail server accepts traffic from the SQL Server.
    – SS_DBA
    Sep 21 '16 at 17:10
  • I hope there is no problem with the mail server. Because it accepts the mails that is sent from INSTANCE2 and delivers it. The problem is with INSTANCE1 which is in the same machine. Sep 22 '16 at 10:26
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.