Database mail issue on 1 server

  • Feeg

    SSCrazy

    Points: 2675

    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
    MCSA DBA 2014

  • Feeg

    SSCrazy

    Points: 2675

    MS SQL 2005 Standard Edition with SP2 x86

    Microsoft Windows Server 2003 with SP2

    MCITP: Database Administrator 2005
    MCTS DBA 2008
    MCSA DBA 2014

  • Feeg

    SSCrazy

    Points: 2675

    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
    MCSA DBA 2014

  • Feeg

    SSCrazy

    Points: 2675

    **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
    MCSA DBA 2014

  • Feeg

    SSCrazy

    Points: 2675

    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 DBA 2008
    MCSA DBA 2014

  • jdneilso

    Old Hand

    Points: 300

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply