Monday, 5 November 2012

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



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!

10 comments:

  1. Hi,

    Please check also below mentioned URL that also help for Mail troubleshooting.

    https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/e4cf06f5-9a36-474b-ba78-3f287a2b88f2.mspx?mfr=true

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Server buddies provides Quality remote server management including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support for a large variety of customers at affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.
    .........

    ReplyDelete
  5. ARINET DBA Services is a Chicago, IL based organization established in 2013. Uniting more than 100+ years of combined involvement in giving quality Oracle database Support administrations to American organizations, we've given the diverse options about remote dba masters, remote dba services, remote dba reinforce, remote database, prophet remote, dba remote, database association, prophet dba reinforce, Oracle sponsorship and Oracle Consultants.

    ReplyDelete
    Replies
    1. You can just Kiss our butts for advertising in this post.

      Delete
  6. Very nice article, thx!
    SQL server 2016 - if you install without framework 3.5 (i think its default), mail not working. Mail status is unsent, no errors, nothing.
    Solution (hope) is kumulative update 2 for SQL server.

    ReplyDelete
  7. Thanks for the article. I linked it at http://expressnetsolutions.com/sqldch/?p=683 as it pointed me to the 'databasemail.exe' as being the problem. The comment from PanKostka was helpful also, wondering if CU2 resolved the issue.

    ReplyDelete
  8. Thanks for providing this informative information you may also refer
    http://www.s4techno.com/blog/2016/08/02/sql-interview-questions/

    ReplyDelete