Database Mail failing with “The service queue "ExternalMailQueue" is currently disabled.” message

Some of you may experience problems while using Database Mail on SQL Server 2005 or SQL Server 2008 which will not be able to send emails to the database users. The SQL Server error logs will log following errors:

 

The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'

 

Along with this error you may also see following errors from Database Mail Logs:

 

Date 10/25/2009 12:20:01 AM

Log Database Mail (Database Mail Log)

Log ID 456

Process ID 6532

Last Modified 10/25/2009 12:20:01 AM

Last Modified By  abc\xyz

Message

Exception Information

Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException

Message: The read on the database failed. Reason: Failed to load Msxmlsql.dll.

Data: System.Collections.ListDictionaryInternal

TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)

HelpLink: NULL

Source: DatabaseMailEngine

StackTrace Information

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueItemFromCommand(SqlCommand c)

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueData(Int32 receiveTimeoutSec)

   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)

 

 

This error will come with Windows Server 2003 SP2 or Windows Server 2000 SP4 on SQL Server 2005 or SQL Server 2008.

 

A very good explanation about this error is given in the KB article 941105 @ https://support.microsoft.com/?kbid=941105 and most of the times the issue will be resolved by applying the windows hotfix on top of Windows Server 2003 SP2 or using the Workarounds given for Windows Server 2000 SP4.

 

The intention of writing this content is to make you aware of the fact that even after applying the hotfix or following the workaround it could be possible that you keep on getting the errors and emails will fail. Now to resolve the issue you may do following checks and implement the relevant plan:

 

NOTE: You should follow the below steps only after applying the Windows hotfix given in KB 941105.

 

1. Verify the version of MSXMLSQL.DLL under 90\Shared folder. In my environment the version was 4035 because I was using SQL Server 2005 Service Pack 3. It could be different in yours. The msxmlsql.dll version should be equivalent to the last updated patch or service pack you have installed.

 

2. You could also use following script to verify MSXMLSQL.DLL is valid:

 

declare @var1 int

declare @var2 int

exec @var2 = sp_xml_preparedocument @var1 output, '<abc><name>Sumit</name></abc>'

select 'Return value from sp_xml_preparedocument is: ' , @var2

select * from openxml (@var1, '/abc', 2) with (Name varchar(20) 'name')

exec @var2 = sp_xml_removedocument @var1

select 'Return value from sp_xml_removedocument is: ' , @var2

 

If the DLL is valid this will give you output like the following:

 

--------------------------------------------- -----------

Return value from sp_xml_preparedocument is: 0

 

(1 row(s) affected)

 

Name

--------------------

Sumit

 

(1 row(s) affected)

                                          

-------------------------------------------- -----------

Return value from sp_xml_removedocument is: 0

 

(1 row(s) affected)

 

If MSXMLSQL.DLL is not valid, you need to work towards resolving this and then proceed further.

 

3. Once you know that MSXMLSQL.DLL is valid, then go ahead and delete the existing mail profile and recreate a new one. This will address your corrupted database mail profile if any.

 

Now, it could be possible that even after implementing the above steps you will not be able to send emails and may encounter the errors like the following:

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=14641&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

 

To fix the above error please follow the below mentioned plan:

 

1. Run the following command from New Query Window:

 

Use MSDB

Select count(*) from ExternalMailQueue 

 

2. If you see Non Zero output of the above statement, attempt to clean the queue using the below script:

 

We can use the following T-SQL to save all the unsent items and then cleanup the Mail Queue. In case any email needs to be resent, they can do so after DB Mail is working successfully:

Use MSDB

select * from msdb.dbo.sysmail_unsentitems

Disclaimer: When you run the below step it will clean all the emails that are queued in the ExternalMailQueue. Please do all necessary checks about the important emails in the queue before executing this step.

 

Use MSDB

ALTER QUEUE ExternalMailQueue WITH STATUS = ON

set nocount on

declare @Conversation_handle uniqueidentifier;

declare @message_type nvarchar(256);

declare @counter bigint;

declare @counter2 bigint;

set @counter = (select count(*) from ExternalMailQueue)

set @counter2=0

while (@counter2<=@counter)

begin

receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue

set @counter2 = @counter2 + 1

end

 

Once you are done with the above step check the status of the ExternalMailQueue again. This time it should return 0 records. You can also modify the LOOP, if you already know how many TOP N messages to cleanup.

 

3. Then run following stored procedure:

 

Use MSDB   

EXEC sysmail_start_sp

 

This SP will start Database Mail by starting the Service Broker objects that the external program uses.

 

4. Now test the Database Mail and verify that emails are flowing across.

 

Disclaimer: Please note that this is a specific scenario which we have experienced on one of the production systems. Please use this article only in cases where you encounter the specific error messages given in the article and when KB 941105 did not provide any relief. As I have written earlier, please do all necessary checks about the important emails in the queue before cleaning up the mail queue completely.

 

I hope this will resolve the DB Mail issues!!

 

Sumit Sarabhai
SE, Microsoft SQL Server

Reviewed by

Mukesh Nanda
TL, Microsoft SQL Server