How do I get dbmail to process items from the queue for SQL Server 2005?

Question

When I use the spsenddbmail stored procedure, I get a message saying that my mail was queued. However, it never seems to get delivered. I can see them in the queue if I run this SQL:

SELECT * FROM msdb..sysmail_allitems WHERE sent_status = 'unsent'

This SQL returns a 1:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

This stored procedure returns STARTED:

msdb.dbo.sysmail_help_status_sp

The appropriate accounts and profiles have been set up and the mail was functioning at one point. There are no errors in msdb.dbo.sysmaileventlog.

Answer 1

Have you tried

sysmail_stop_sp

then

sysmail_start_sp

Answer 2

@Stu - Yes, start/stop didn't work.

However, I restarted the SQL Server service and that seems to have caused it to start functioning again. I'd still like to know if there is a logical explanation for what happened. I obviously can't plan on being able to restart SQL Server at will once this moves to production. Furthermore, who knows how long it would be before I realized I wasn't getting emails that I should be getting.

I hope that I'm missing something in the status checks that would actually tell me that there is a problem. It seems odd that everything I can find would indicate that dbmail is up and running but mail is not actually being sent. I don't recall ever having similar issues with SQL Mail in SQL Server 2000.

Answer 3

Could be oodles of things. For example, I've seen (yes, actually seen) this happen after:

• Domain controller reboot
• Exchange server reboot
• Router outage
• Service account changes
• SQL Server running out of disk space

So until it happens again, I wouldn't freak out over it.