Database Mail

Been into configuring this feature lately.

Though this feature is as easy setting up as starting the management studio, there are still some issues related to it that might kill your time.

Today i’m providing a list of Database Mail scripts I use when troubleshooting issues. 

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

1. 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:

2. EXEC msdb.dbo.sysmail_start_sp

NOTE: It can also be stopped by simply running

3. EXEC msdb.dbo.sysmail_stop_sp

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

4. 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:

5. 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

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

Check the mail eventlog for further errors

7. SELECT * FROM msdb.dbo.sysmail_event_log;

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

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

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

9.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

10. 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

11. Most importantly, if everything seems fine and still the mail status is shown as failed or unsent,

just check whether the "DATABASE ENGINE" service and "SQL AGENT" service are running under same  logon.

They running under different logons might cause authentication problem at mail server.