Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database mail issue on 1 server


Database mail issue on 1 server

Author
Message
Feeg
Feeg
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 2003
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 DBA 2008
Feeg
Feeg
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 2003
MS SQL 2005 Standard Edition with SP2 x86
Microsoft Windows Server 2003 with SP2

MCITP: Database Administrator 2005
MCTS DBA 2008
Feeg
Feeg
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 2003
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 DBA 2008
Feeg
Feeg
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 2003
**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 DBA 2008
Feeg
Feeg
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 2003
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 CoolCool

MCITP: Database Administrator 2005
MCTS DBA 2008
jdneilso
jdneilso
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 37
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search