patternsqlMinor
DatabaseMail process is shutting down
Viewed 0 times
shuttingdowndatabasemailprocess
Problem
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.
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.
Is there is any solution for this? Thanks in Advance.
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.
Solution
I have had the same issue and I followed these steps to resolve that.
-
and check if length > 0 then DELETE QUEUE items.
-
Use the following query to delete queue items:
-
-
EXEC msdb.dbo.sysmail_help_queue_spand check if length > 0 then DELETE QUEUE items.
-
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-
EXEC msdb.dbo.sysmail_start_sp, start the process and check.Code Snippets
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @GETDATE;
GOContext
StackExchange Database Administrators Q#150294, answer score: 2
Revisions (0)
No revisions yet.