Database Mail – What could possibly go wrong?

Fans of SQL Server will know that the answer is LOTS!  So I shall try to make some kind of sense of it.  I hope you find it helpful.

If you are in a rush, skip to the end where I have listed three things to make sure

Start by sending a test message to yourself

Easiest way I think is to open Management in SSMS, right click on database mail, and select Send Test E-Mail. Have a look in your inbox.
But if you want to do it with a bit of T-SQL, why not?

--send test message
USE msdb
GO

EXEC sp_send_dbmail
@profile_name='H2SO4',     -- whatever the mail profile is called

@recipients='jack.whittaker@mycompany.co.uk',  -- YOUR email, duh
@subject='Test Message',
@body='If you can read this email, your database mail is working';
go

If you find that message in your inbox, well done, go and have a nice cup of tea. 

No sign of your message?
Check out your Junk / Deleted Items folders – I did a lot of troubleshooting today, as emails were being  caught by a Junk rule and being automatically moved to my Junk folder.  If you have set up rules to send automated messages to a folder, check there too. 

Is it a permissions issue? 
Add this little bit of code before the above script and try again. 

EXECUTE AS LOGIN = 'SA'

Check the Logs

USE MSDB
GO

SELECT

       sai.sent_status

       ,sai.subject

       ,sai.body

       ,sai.last_mod_date

       ,sai.recipients

       ,sel.description as Log_Message 

FROM dbo.sysmail_allitems as sai

INNER JOIN dbo.sysmail_event_log AS sel

    ON sai.mailitem_id = sel.mailitem_id;

If all are UNSENT, think about restarting the SQL server agent service

The Event Log may or may not be helpful.  It usually tells you either

1)                DatabaseMail process is started

2)                DatabaseMail process is shutting down 

You can ignore these by adding a where clause, but the started and shutting down messages indicate that it is working normally.

use msdb

go

select *

from dbo.sysmail_event_log

--where event_type != 'information'

order by log_id desc;

There are a couple of other things you can look at, but probably not helpful if you have already looked at sysmail_allitems

-- list all emails

SELECT sent_status, *

FROM dbo.sysmail_mailitems;

--List all SENT emails

SELECT sent_status, *

FROM dbo.sysmail_sentitems;

And one of these things might give you a clue

--Should be 1 ie Yes

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

--should be started

 EXECUTE msdb.dbo.sysmail_help_status_sp;

--state should be active

 sysmail_help_queue_sp @queue_type = 'Mail' ;

 --ensure that sysmail has been started

 EXECUTE dbo.sysmail_start_sp;

Things to make sure

A)     Database Mail insists that you have .NET Framework 3.5 on your server.  You might notice that you have .NET Framework 4.5, so you don’t need the old version, right?  WRONG Check out how to do it here

B)     If you can send an email from the Database Mail, but not from SQL Agent jobs, you may have forgotten to enable a mail profile for alerts.

1. Right click on "SQL Server Agent"
2. Then "Properties".
3. Then go to "Alert System" section.
4. Tick the "Enable mail profile" box.
5. Then "OK".         
6. MUST do - Restart the SQL Server Agent service. 
   

C)     And if Database Mail seems to be sending, but you are not receiving, check again that your message is not being caught by a filter.  Sysadmins will be able to look for the message in Exchange.  You can check it by sending a test message to a colleague.