As part of my blogging I’ll be periodically providing readers with a collection of my scripts which I use on a daily basis (collected over the years) and hopefully others can gain from these and utilise them going forward.

Today i’m providing a list of Database Mail scripts I use when troubleshooting issues. These are predominantly SQL Server 2005 but I believe will work on SQL Server 2008 but as I don’t have a 2008 instance available to test I can’t guarantee this!

Firstly, a good place to start is to check the status of your Database Mail:

EXEC msdb.dbo.sysmail_help_status_sp

it may be possible that it isn’t sending mail because it hasn’t been started

Following on from this, if you discover that Database Mail has in fact stopped then you can start it again with:

EXEC msdb.dbo.sysmail_start_sp

NOTE: It can also be stopped by simply running

EXEC msdb.dbo.sysmail_stop_sp

If Database Mail is running then it may be that Service Broker has not been enabled:

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

Another technique I use when troubleshooting Mail issues (if its running and the settings are correct) is to check if anything is actually stuck in the SQL Server Mail system, it can be apparent that mail is sat queued but unable to send. A quick and easy way to determine this is to run:

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

If the value is 0 then you can determine that there isn’t actually anything in the queue. If the value is anything other than 0 then you can investigate this further by running one or all of the below:

Test to see if your mail has actually been sent

SELECT * FROM msdb.dbo.sysmail_sentitems where sent_date > dateadd(d,-1,getdate());

Check the mail eventlog for further errors

SELECT * FROM msdb.dbo.sysmail_event_log;

Check for any mail item which is sitting at the unsent status

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

A favourite of mine, determine the reasons why a mail item has actually failed

SELECT items.subject,
    items.last_mod_date
    ,l.description ,*
FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > getdate()-1

The script above tends to be the ideal script to run in order to find out why an email isn’t being sent, i tend to find 90% of my issues by running that script!

Something else worth checking is whether the user actually has permissions to be sending emails via SQL Server:

Check members of the DatabaseMailUserRole

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole'

These are just a handful of queries that can be ran when troubleshooting Database Mail issues, by no means is it a complete list but from my experience i rarely have to go elsewhere. There has been times when i’ve had to venture into amending retry attempts, querying sys.dm_broker_queue_monitors and sys.service_queues but this has been very rare circumstances so I’ve not included the queries for these

If anyone has anything they feel worthwhile adding to this then feel free to email me or comment and I’ll add them if relevant.