Troubleshoot DBMail in Sql Server 2005

Question

On a SQL Server 2005 machine, I have DBMail setup and correctly configured. I am able to use "Notify Operator" and EXEC msdb.dbo.sp_send_dbmail to queue emails.

But the problem is the queued mails never go through. I see 10 emails in the queue when I query

SELECT * FROM msdb.dbo.sysmail_unsentitems

But if I restart the SQL Server service, all mails go through and I receive emails in my inbox.

So far this is the only pattern I could find.

So my question is: is there a way to troubleshoot DBmail? I read some steps on MSDN, but they are not of much help. Any URLs to articles that could help are much appreciated.

Answer 1

Have you start the service broker on MSDB ? You need the service broker on to be able to process mail queue.

Read this post on Stackoverflow. An other link that may help you is how to troubleshoot a queue in sql. You can also use this link to get your service broker up and running on MSDB.

Hope that those links may help you.

Answer 2

Thank you for all your comments. I found the root of the problem.

Sql Agent >> Properties >> Alert System (tab) >> Enable Mail profile (check) And select appropriate mail system and add a mail profile.

MSDN Link for DB Mail

This took care of the problem.

When I look at Sql Agent Logs it had a log entry as "Unable to start mail session (reason: no mail profile defined)". Using this key word, I was able to search online and get some help.