Database Mail Queue stuck in INACTIVE State - can't get Database Mail to work on Server

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Please post to the following:

    http://www.sqlservercentral.com/Forums/Topic1276632-1550-4.aspx#bm1276973

    I execute the following statement to check the status of Database Mail:

    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

    The mail queue should have the state of RECEIVES_OCCURRING

    So I tried stopping the queue using sysmail_stop_sp and then starting the queue using sysmail_start_sp but it does not change the State.

    Does anyone know what could be causing this problem and what I can do to resolve this issue?

    Any help would be greatly appreciated.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Lowell

    SSC Guru

    Points: 323450

    I believe you are incorrect; the default state would be INACTIVE unless you query the queue at teh exact moment you are actually sending an email.

    all four of my servers i just checked all show INACTIVE, nad one ran an email campaign this morning at 7am wiithout errors.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Lowell (3/23/2012)


    I believe you are incorrect; the default state would be INACTIVE unless you query the queue at teh exact moment you are actually sending an email.

    all four of my servers i just checked all show INACTIVE, nad one ran an email campaign this morning at 7am wiithout errors.

    Thanks for correcting me.

    I sent mail and checked it and the status and it was still INACTIVE.

    The problem is that all of the items are in the queue and none have been sent.

    I tested SMTP Mail with Telnet and it worked.

    Any suggestions?

    Thanks again.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Lowell

    SSC Guru

    Points: 323450

    i found some interesting stuff about service queues not having a unique GUID over here:

    http://stackoverflow.com/questions/1127404/service-broker-with-sql-server-2005-messages-stuck-in-queue

    I know i've had a situation a couple of times where the service just doesn't seem to send mail, and the procs for starting and stiopping the mail service were ineffective; the only solution was to stop and start the SQL service, which fixed the issue immediately.

    I've never had the service in that situation where it was stuck, AND i could write any diagnostics to detect what the core issue was; I've had to bounce the serice immediately for biz reasons.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Welsh Corgi

    SSC Guru

    Points: 116520

    I had already tried restarting the SQL Server Service but no luck.

    I used the same script to configure Database mail that I used on another new Server a month ago and I did not have any problems.

    I'm feeling like what's wrong that I can't figure this out.:blush:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • ajosephson

    SSC Enthusiast

    Points: 109

    I ran into a similar problem with mail not being sent even though everything is configured correctly. The problem turned out to be the name of my mail profile! I had a space in it. So when I changed "Mail Profile" to "MailProfile", everything worked fine and the mail queue state correctly changed from "INACTIVE" to "RECEIVES_OCCURRING".

  • C# Gnu

    SSCertifiable

    Points: 5973

    Just to say restarting sql server services also solved this problem for me.

  • lsalih

    SSCertifiable

    Points: 6142

    I know this is an old post, but I wanted to add this since lately we have been dealing with few failures of SQL Mail not sending email.

    1) If EXECUTE dbo.sysmail_start_sp does not work, then go to task manager and stop/start the databasemail process. The process name is DatabaseMail.exe. Once you started the process, send test mail using the profile(s) you have to clear the unsent mail queue.

  • eggerts2k

    SSC Enthusiast

    Points: 125

    I realize this is an old post, but the issue for me was the .net 3.5 framework was not installed (Win2K12R2/SQL2016 - new installation) and as such the databasemail.exe was unable to run.

  • OracleGuy

    SSC Rookie

    Points: 48

    @eggerts2k     It may have been an old post but THANKS for the note regarding the .Net 3.5 installation.  I spend 4 hrs trying to figure out why I was having this problem after enabling mail on a 2012 server I inherited.  I saw your note and checked the installed features. Net4.0 was installed but 3.5 was not.  5 minutes later 3.5 was installed and the problem was resolved.

  • claytons

    Newbie

    Points: 9

    @eggerts2k Seconding this!
    Quick install of .net 3.5 and my problem was solved.

    Clayton

  • richardmgreen1

    SSCrazy Eights

    Points: 9907

    I know this this is an old thread, but thought this may be worth a mention.

     

    I have the same issue on a SQL2016 / Windows Server 2012R2 box.

     

    I'd tried everything (up to and including deleting and rebuilding the mail profile!).

     

    I finally got round to checking Task Manager (thanks to Isalih for this one) and noticed that I'd got 2 instances of DatabaseMail.exe running.

     

    I killed them both, stopped and restarted mail in SSMS (using EXEC dbo.sysmail_stop_sp and EXEC dbo.sysmail_start_sp) and all is now good.

     

    Hope this helps someone.

  • mkirschner

    Grasshopper

    Points: 11

    Thank you very much for this thread, which gave me sufficient hints to discover yet another solution (as I wasn't able to install .NET 3.5 framework):

    https://support.microsoft.com/en-us/help/3186435/sql-server-2016-database-mail-doesn-t-work-when-net-framework-3-5

    Their workaround #1 (simply recreating the missing DatabaseMail.exec.config file) worked like a charm for me. See instructions in the link above.

    PS: There's nothing like discovering a sql agent job has been silently failing because the failure notification email wasn't getting sent...

    • This reply was modified 8 months ago by  mkirschner. Reason: Correction
  • chuck kruelle-156702

    Newbie

    Points: 1

    "SSCrazy Eights" solution worked for us.   in jan 2020 and feb 2020 our database mail was in a hung state and not automatically sending mail.   We rebooted after patching and fixed us temporary in jan 2020.   We have been manually clicking the databasemail.exe to get our mail daily.   so we kept researching and found SSCrazy Eights solution.   Thanks, this worked for us.

    “I finally got round to checking Task Manager (thanks to Isalih for this one) and noticed that I'd got 2 instances of DatabaseMail.exe running.

    I killed them both, stopped and restarted mail in SSMS (using EXEC dbo.sysmail_stop_sp and EXEC dbo.sysmail_start_sp) and all is now good.”

Viewing 14 posts - 1 through 14 (of 14 total)

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