Database mail queue stays INACTIVE
Question
Wednesday, September 26, 2012 1:55 PM
Hello
I tried for hours to get my notifications working, without success, on SQL Server 2008R2 Web Edition 64b
I send mail with the test :
USE msdbGOEXEC sp_send_dbmail @profile_name='ARRIERE',@recipients='mai@mail.tld',@subject='Bonjour ',@body='Congrates Database Mail Received By you Successfully.'
But this mail seems to be never sent.
With:
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
The queue state is always INACTIVE, and length is growing everytime I send a email.
I tried to restart the mail system some times :
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;
The states goes RECEIVE OCURRING, but after some seconds and without intervention, it goes back to INACTIVE.
I tried to rester SQL Agent, SQL Server, the server himself, but no luck.
The broker is activated (SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;)
I disabled every firewall, antivirus and others (by the way, I can do "telnet smtp.domain.tld 25" and send a mail with telnet.
With
SELECT * FROM msdb.dbo.sysmail_allitems;
I see all my mails with state "unsent".
Nothing logged in Event Viewer, SQL Events, and in sysmail_event_log I only get "Activation successful".
I configured the local IIS SMTP as a relay as said here : http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f9744284-a9b6-4bfb-81b9-c4833f282ad0
I cleaned the mails with:
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE()
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;
GO
=> Nothing more.
Can I do something more?
Thans for your help.
Regards
All replies (25)
Tuesday, October 9, 2012 9:31 AM âś…Answered
Hello,
The probleme was solved.
For test, I granted the service account running SQL Engine to Administrators Group then restarted the service MSSQL and it worked.
With process Monitor, I detected some Access denied in registry.
It's seem that sysadmin role was not enough.
Regards.
Thursday, September 27, 2012 8:19 AM | 1 vote
Hi X.Perignon,
Please refer to this thread to troubleshoot your problem:
The solution is turn on the SQL Server Browser service.
And one thing need to be noticed, service broker in SQL Server web edition is only supported for client. Client-only mode is that you can program a single instance of SQL Server with Service Broker however you like. However, when communicating between multiple instances that use Service Broker, you must have at least one, "non-client-mode" instance in the mix (e.g. you could not do cross-instance messaging using only two Express instances).
Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thursday, September 27, 2012 8:49 AM
Hello,
Thanks you for your answer. I already seen this thread, but I forgot to mention it in my post, apologies.
the Browser was already started... restarting it didn't change anything.
Indeed, I have two instances on this server. The only one purpose of sql mail on this server is to send a mail after each work finished.
No communication between these two instances.
Have a nice day
Regads
Friday, September 28, 2012 7:36 AM
X.Perignon,
Please check these items:
1. Checked the SQL Server agent alert sytem configuraion to understand if the Database mail is enable or not. Right click SQL Server agent -> properties -> Alert system.
2. Run sp_configure, check Database mail is enabled. Sample is: Database Mail XPs 0 1 1 1
3. Checked the SQL Server MSDB database for service broker as below:
select is_broker_enabled,* from sys.databases
4. Checked the status of the database mail and it is started.
EXECUTE dbo.sysmail_help_status_sp
If all above is already configured, please try to:
1. Create a mail account (domain account) dedicated for DB Mail
2. Change the SQL service account to the mail account
Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Tuesday, October 2, 2012 1:17 PM
Hello,
I replace X.Perignon for the issue.
Iric, I tested all step but same issue, all mail stuck in queue at "unsent" status :(
Any idea ?
Have a nice day
Regards
Wednesday, October 3, 2012 1:42 PM
have you seen any errors in database mail logs ?
Is it working before or its new setup?
I know its not a good solution but I stuck with this one time :-
1) I clear all mail profiles, pending mails & disable database mails
2) restart the server having sql server installed
3) Create new profile from start & it works
There are several things that cause issue.
Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click
Mark as Answer and
Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.
Thursday, October 4, 2012 4:38 PM
Hello,
it's working very well before. :(
Nothing logged in Event Viewer, SQL Events, and in sysmail_event_log I only get "Activation successful"
I created a new profile with an another mail SMTP but no luck.
I will try your solution.
If you are any suggestion, you are welcome :).
Regards.
Friday, October 5, 2012 1:54 PM
can you check the connectity with smtp server. ping & telnet on port 25.
Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click
Mark as Answer and
Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.
Friday, October 5, 2012 9:39 PM
Try sending a test mail from Management - Database Mail and see if that reaches you or not.
if not then check this table for the reason for it.
select * from sysmail_sentitems
order by sent_date desc
Monday, October 8, 2012 2:59 PM
Hello,
I can send successfully a mail test from telnet command.
I tried to send a mail test from management - Database Mail but not working.
In:
select * from sysmail_sentitems
order by sent_date desc
- > I have nothing :-(
But at SELECT * FROM msdb.dbo.sysmail_allitems; I have my mail at "unsent" status.
Regards.
Wednesday, July 16, 2014 2:47 PM
Hello everyone,
First of all, thanks for the attention.
I have the same issue here in my SQL 2008 server, the queue state of all e-mail sent are INACTIVE. But the service account running MSSQLSERVER is the local system account.
I think that this account already has administrative properties. Must also put it in the administrators group? How should I proceed?
best regards,
Marcelo.
edit: i used the process monitor, not detected "access denied"...detected only "name not found" and "path not found"
Att, Marcelo E. Rodrigues
Friday, September 9, 2016 6:16 AM | 15 votes
Hi X.Perignon,
I know this is an old thread, but I had the very same problem with Windows Server 2012 R2 and SQL Server 2016. And after a lot of efforts and tracking down the root cause of the issue, I found the issue is the absence of .Net Framework 3.5.
SQL server 2016 needs not only .Net framwork 4.6 that is in the Microsoft website as a pre-requisite for SQL server 2016, but you have to enable .Net framework 3.5 as well. This is how to if you don't know already:
1- Enable/Install .Net Framework 3.5 https://technet.microsoft.com/en-us/library/dn482071.aspx
2- After installation if the emails in the queue are not sent out, you need to restart SQL Server Engine. For doing so again if you don't know: Open "SQL Server Configuration Manager > SQL Server Services > right-click on "SQL Server (yourInstanceName)" >Restart
I hope this help those who have the same problem.
Kind regards,
Mohammad
Monday, September 12, 2016 10:46 PM
Thank you Mohammad! You saved me from a lot of additional grief! That was the fix for my exact problem, as well.
Friday, September 23, 2016 1:44 PM
THANK YOU Mohammad! I've searched for hours on this problem, and you are the only person I've seen explain the need for .Net 3.5. That fixed it, running SQL Server 2016 on Win 10.
Paul
Wednesday, October 5, 2016 8:22 AM
Thanks, saved me hours of headache!! That does the trick on windows server 2012 and sql server 2016...
Paolo
Monday, October 17, 2016 4:38 PM
I bumped into a similar issue. it turned out the DatbaseMail.exe had crashed and could not be restarted by SQL Server broker.
It would work when I started it from the command prompt. Until we could restart SQL Server I needed to schedule it.
HTH
Wednesday, December 14, 2016 10:57 PM
Mohammad, thanks. And, someone probably needs to clear the rest of these answers. It would certainly safe time for others running into this.
Tuesday, April 11, 2017 4:56 AM
Thanks Mohammed for this solution, it worked well.
Tuesday, May 2, 2017 8:43 PM
I realize this is an old thread, but I found it troubleshooting this problem and the solution I found was a bit different from some of those listed here.
SQL 2008 R2 Standard
Windows Server 2008 R2 Standard.
I was able to run DatabaseMail.exe manually from an administrative login and the mail messages that were queued up were sent.
The windows account under which SQL Server was running (Network Services) did not have security access to 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config'. When SQL server tried to run DatabaseMail.exe it threw an exception which appeared to get swallowed somewhere along the line as I didn't find the exception messages in any of the database logs etc.
How I did end up finding this was to try and run DatabaseMail.exe From the query windows using xp_cmdshell, which illuminated the stack trace.
Once I added the Network Services account granting it read access to the machine.config file, SQL server mail test sends started working.
Monday, June 12, 2017 9:17 AM
Thank you Mohammad
Enable/Install .Net Framework 3.5 solve my Problem. Running Windows Server 2016 and SQL Server 2016.
Kind regard
Redouane
Friday, December 22, 2017 7:10 AM
Wow Man
Thank God I came across this Post.
It Works!
However, make sure you restart everything, Server and Everything.
Tuesday, January 9, 2018 2:45 PM
Be carefull for new installations.
Scenario:
1. Windows 2016 core
2. Framework 4
3. SQL server 2016 (13.0.1601.5)
4. Sysmail will stay inactive
Reason: SQL installation works fine since Framework 4 is present BUT sysmail needs Framework 3.5 !
Do not forget Framework 3.5 (it costed me 2 days of research!)
Philippe
Monday, April 2, 2018 4:05 PM
That was the fix for my exact problem!
Tuesday, April 17, 2018 3:39 AM
Thank you SO much Mohammed for this post! I spent an entire day looking for this answer.
Friday, August 10, 2018 1:48 AM | 1 vote
It's better to use method 1 from https://support.microsoft.com/en-ph/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe