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
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The read on the database failed. Reason: Failed to load Msxmlsql.dll.
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)
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 @ http://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)
(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)
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641)
To fix the above error please follow the below mentioned plan:
1. Run the following command from New Query Window:
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:
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.
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)
receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue
set @counter2 = @counter2 + 1
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:
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 Reviewed by Mukesh Nanda
SE, Microsoft SQL Server
TL, Microsoft SQL Server