Last week we blogged about “How to configure Database mail” – this week we’ll have a closer look at what to do when everything looks fine, but no mail is flying through the system – what is wrong?.

After testing, if you are unable to receive notification emails, you could use the below Database Mail views for troubleshooting. This first one contains one single row for each message successfully sent by Database Mail.

select * from msdb.dbo.sysmail_sentitems 

This next is the complete opposite, here we’ll se a row for each message with an unsent or retrying status.

select * from msdb.dbo.sysmail_unsentitems 

And finally here a view that shows one row for each Database mail message with status failed

select * from msdb.dbo.sysmail_faileditems 

Personally I’m not a big fan of the GUI, if You share that passion with me, here is a view that will give You the Database Mail event log.

select * from msdb.dbo.sysmail_event_log 

And finally, here is the view that shows it all.

select * from msdb.dbo.sysmail_allitems 

Database Mail has two queues, a status queue and a mail queue. The mail queue tells us the number of mail in queue waiting to be sent. The status queue stores the status of items that have already been sent. Here is a Stored Procedure that can show You the status of the two queues.

exec msdb.dbo.sysmail_help_queue_sp; exec msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'; exec msdb.dbo.sysmail_help_queue_sp @queue_type = 'status'; 

Did this not help You? Well here is a link to the official Microsoft documentation, that might help – happy troubleshooting.