January 11, 2011 at 2:15 am
Hi guys, an issue started on 29 Dec 2010. This is a QAS server which I use to send my SQL Overview mails http://www.sqlservercentral.com/articles/Administration/63042/
this server sends mail via an exchange server which worked fine until 29th. The PRD server is on the same domain and there is no issues sending mails.
Since the 29 Dec 2010 errors
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'
in SQL Server log every 5 seconds
We checked McAfee and made sure that 'Prevent mass mailing worms from sending mail' is unchecked
I also receive
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641)
I then run
sysmail_start_sp
after I run
alter queue ExternalMailQueue with status = on
I check the status with
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
and
EXECUTE dbo.sysmail_help_status_sp;
and all is fine but I still can't send mails out anymore. There is nothing in the Security log beside an ANONYMOUS LOGOFF on 29th
Application log has
Database Engine Instance=QAS_SErver;Mail PID=13976;Error Message:The connection is not open.
when the mails to send.
What else can I check?
There is nothing in the Dbmail log, only mail queue has started messages when I ran it manually. no mail queue has stopped messages
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
January 11, 2011 at 4:32 am
MS SQL 2005 Standard Edition with SP2 x86
Microsoft Windows Server 2003 with SP2
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
January 11, 2011 at 11:42 pm
Database mail has stopped once again this morning ...
What can cause it to stop?
I ran
sysmail_start_sp
alter queue ExternalMailQueue with status = on
an I'm sending mails again ...
any leads?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
January 14, 2011 at 2:32 am
**UPDATE **
I checked the PRD server where the mails are still working and ran
SELECT * FROM sys.service_queues
execute_as_principal_id = 1 on sp_ExternalMailQueueListener and sp_sysmail_activate
On the QAS server
execute_as_principal_id = -2
and checked http://msdn.microsoft.com/en-us/library/ms187795.aspx
ID of the EXECUTE AS database principal.
NULL by default or if EXECUTE AS CALLER.
ID of the specified principal if EXECUTE AS SELF EXECUTE AS <principal>.
-2 = EXECUTE AS OWNER.
I think this is the issue as this is the difference between the 2 servers.
ALTER QUEUE [InternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_ExternalMailQueueListener],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
ALTER QUEUE [ExternalMailQueue] WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [dbo].[sp_sysmail_activate],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
January 18, 2011 at 5:55 am
Fixed it :-P:-P
there was a mail stuck in the queue which I removed via
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
the --Start mail services
exec sysmail_start_sp
and viola :cool::cool:
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 24, 2015 at 11:40 am
When I had messages stuck in the Queue...
I tried stopping with sysmail_stop_sp and then starting with sysmail_start_sp. Both commands completed successfully, however, the items still remained stuck in the queue.
I then noticed in the Windows Task Manager, the DatabaseMail.exe process would not disappear when running sysmail_stop_sp. I then killed the DatabaseMail.exe process in Windows Task Manager, then ran sysmail_start_sp, and boom, messages started flowing again.
I hope this helps as it took me about 5 hours to get here!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply