Database mail queue stays INACTIVE

Question

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 msdb
GO
EXEC sp_send_dbmail @profile_name='ARRIERE',@recipients='[e-mail]',
@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

Answer 1

Hi X.Perignon,

Please refer to this thread to troubleshoot your problem:

http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/e9aea42f-0382-4cee-b3b4-8aa62842d44e

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

Answer 9

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.

Answer 10

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.