8

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.

Sandeep Kumar M
  • 4,642
  • 3
  • 32
  • 35
UB01
  • 937
  • 2
  • 9
  • 18

2 Answers2

8

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.

Community
  • 1
Nico
  • 1,062
  • 1
  • 9
  • 18
  • Thank you, I will try your suggestions and post my results. – UB01 May 12 '11 at 02:24
  • yes, I did try all of those. Everything returned as STARTED or running okay. Looks like the problem is with Sql Agent Alert System settings. See below – UB01 May 12 '11 at 18:04
5

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.

UB01
  • 937
  • 2
  • 9
  • 18