Freigeben über

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

  • Artikel

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

Comments

  • Anonymous
    June 07, 2010
    Not all files were copied to the second node in my cluster install.  Had to copy all the files in the resource directory and it worked.

  • Anonymous
    October 17, 2011
    How I can check version of msxmlsql.dll with service pack??? Please give us steps to check with service pack. Thank you Sureddy

  • Anonymous
    April 16, 2012
    Hi, Thank you very much for your info it helped me to solve my problem. In my case, I had to replace the 'msxmlsql.dll' and 'msxlsql.rll' files. In the Mail Data Log; I could see that this file was not succesfully loaded: ================== Exception Type: System.Data.SqlClient.SqlException Errors: System.Data.SqlClient.SqlErrorCollection Class: 16 LineNumber: 1 Number: 6610 Procedure: sp_xml_preparedocument Server: EVMAC-WS01COLTPROD State: 1 Source: .Net SqlClient Data Provider ErrorCode: -2146232060 Message: Failed to load Msxmlsql.dll. Data: System.Collections.ListDictionaryInternal TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean) HelpLink: NULL ====================================== You can use Process Monitor (technet.microsoft.com/.../bb896645) to check wich file are being accessed my SQL SERVER; to check the exact location of your msxmlsql.dll file. After I replaced these files, I restarted SQL SERVER, ALSO deleted SQL profile and created a brand new one. I also clenar the mail queue with the above script. At the end, everything worked

  • Anonymous
    April 16, 2012
    Wow, sorry for my grammatical mistakes of my message above, I wrote too quick :(

  • Anonymous
    June 12, 2012
    Thank you for report

  • Anonymous
    August 22, 2012
    In addition to above error I am getting this error as well. Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: The read on the database failed. Reason: The error description is 'Whitespace is not allowed at this location.'. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)

  • Anonymous
    July 30, 2013
    I am really appreciated of you.

  • Anonymous
    October 11, 2015
    I have a problem, our database is suppose to retrieve info from our online booking on our website, and then write it to the database. the only thing that comes through is the email notification of the booking. if you are in the database and try to retrieve it, it says White Space not allowed at this location. How do I fix it?

  • Anonymous
    October 12, 2015
    @Grant - You need to check sp_send_dbmail procedure in msdb database and look for MailItemId> You would notice a whitespace there. between "<" and "MailItemId>" please remove that and recreate the proc.