Troubleshooting Database Mail - Part II

I started out with a blog about how to set database mail up correctly.  How about when it goes wrong, or fails to work?

I finished Part I with how to send a test message.  Right click on Database Mail and select Send a Test Email.

There's an alternative if you want - use the sp_send_dbmail stored procedure.   There's no great benefit in using this at the moment, but just for completeness:

EXEC msdb..sp_send_dbmail

       @profile_name='ServerName',

       @recipients='Jack.Whittaker@companyname.com',

       @subject='Test message',

       @body='This is the body of the test message.  Mail is now working.'

Whichever way you do it, an email should arrive in your inbox after a few moments.  Press Send and Receive if nothing happens for a while.  Okay, twiddle your thumbs and go and make a cup of tea.

Still nothing?  You may have a problem.

Check the Logs

First things first - view the database mail logs.  Right click on Database Mail, and select View Database Mail Log.  You are looking for clues here.  See any?  Nope, OK, lets look at some more logs.  

-- list log events

SELECT * FROM msdb.dbo.sysmail_log

GO

-- list error messages

SELECT * FROM msdb.dbo.sysmail_event_log

WHERE mailitem_id >= 1;

Anything?  Hmm - OK, let's look at the mail.  There are several tables which contain mail items.  Just think about the difference between Failed items and Unsent items for a minute.  

--To list all items:

USE msdb

SELECT sent_status, *

FROM sysmail_allitems

GO

--To list the unsent items only:

USE msdb

SELECT sent_status, *

FROM sysmail_unsentitems

GO

--To list the failed items only:

USE msdb

SELECT sent_status, *

FROM sysmail_faileditems

GO

Is your test mail failing?  Then there is probably some sort of error message somewhere.  

Is it unsent?  This may mean that something is wrongly configured. 

Check that you haven't set up two accounts and are using the wrong one:

-- list all accounts

EXEC msdb.dbo.sysmail_help_account_sp;

Can you pass the Buck?

Before looking at the multitude of things it might be, there may be a legitimate reason for your server not being able to send email, even if it is correctly configured.

• Check the exclusion list of your anti-virus software - if AV is active on your server, make sure that databasemail.exe is allowed to send messages.

• Is the server behind a firewall?  If it is, you can send mail until you are blue in the face, they won't get through.  

Next thing to check: can you TELNET to your smtp server on port 25 to make sure nothing is blocked?
Open a DOS Prompt on your server and type telnet mail.domain.ext 25 
It should return something like: 220 smtp esmtp Relay 
Which means that Telnet is working - 220 is the greeting code.

In my case I was able to get a colleague from the network services team to send an email from that box using his email diagnostics tool - that worked fine, so narrowing it down to a SQL Server issue,

Further Reading

Here's a reference to Microsoft's article Troubleshooting Database Mail: General steps - the version for 2008r2 - Database Mail doesn't look much different in the other versions, but if you go to that link it'll take you to the other versions.

Things to Check

Make sure that the login SA is the owner of all the system databases, and especially msdb.
It's probably a good idea if a named individual is not the owner of the user databases - things might go awry when that person leaves.  But for the moment, we are just checking the system DBs.

select name, suser_sname(owner_sid) as Owner 

from sys.databases

Or as so often in SQL Server, there's another way to do it:

-- check database owners

EXEC sp_helpdb

     

  

And if you find any stray databases, bring them back into the fold         

-- change db owner if needed          

USE eWorkDev

GO

EXEC sp_changedbowner [SA]

GO

Are you a member of the sysadmin fixed server role or msdb db_owner role?  This is a fairly safe bet if you are a DBA.  
Otherwise, you should be a member of the DatabaseMailUserRole.

Is Database Mail enabled?

sp_CONFIGURE 'show advanced', 1

GO

RECONFIGURE

GO

sp_CONFIGURE;

GO

RECONFIGURE

GO

sp_configure 'show advanced options', 0

GO

RECONFIGURE

GO

In the results pane, the run_value for Database Mail XPs should be set to 1.

Check DB MAIL Status - Sysmail should be STARTED

EXECUTE msdb.dbo.sysmail_help_status_sp

You can try stopping and then restarting 

EXECUTE dbo.sysmail_stop_sp

EXECUTE dbo.sysmail_start_sp

Check that the Service broker is enabled

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

sysmail_help_queue_sp     This stored procedure looks at the status of the queue, but unfortunately it isn't as helpful as you might think.  It shows the queue as INACTIVE most of  the time, unless it is actually sending or receiving mail right at this moment.  

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

The other possible messages are NOTIFIED (queue has been notified receipt to occur), and RECEIVES_OCCURRING (queue is receiving).

Getting Desperate?

You can always try the old standby - switch it off and switch it on again...

Go to the services on the server - run down to MSSQLSERVERAGENT and stop it,  Then start it again,  

Getting more desperate - locate the MSSQLSERVER service - stop it, start it.  

(Beware of Restart - it occasionally doesn't do anything if the service has crashed).  

Tidying Up

If by now your email has filled up with Unsent or Failed messages, you might like to clear them out with this handy stored procedure:

--clear out unsent messages

EXEC sysmail_delete_mailitems_sp @sent_status = 'unsent'

A good reason for doing this is that eventually you are going to fix this issue, and when you do, a hundred and one test messages are going to suddenly appear in your inbox.

Check the Database Mail program

Verify that the Database Mail External Program is located in the correct directory, 

In my case it's in F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Check the Service Account

Verify that the service account for SQL Server has permission to run the executable DatabaseMail.exe.  Was there a change in the Service account that SQL Server runs under? 

If someone changed the account using the Services MMC rather than SQL Server Configuration Manager, then it may not have the rights required - these are usually set by the SSCM.  Using SSCM, reset the service account to something like NetworkService and restart SQL Server and SQL Agent. Try sending a test mail now.  Then using SSCM, set it back. This should confirm that the service accounts have the necessary rights. 

In my case, it was the Service Account which was the problem.  If you want, you can read an account of the hunt here.  Apart from thanking all involved again, I would like to make the point that you can search Google all you want - it's not as good as setting the problem-solving skills of a team of DBAs to work!