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

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

--To list the unsent items only:
USE msdb
SELECT sent_status, *
FROM sysmail_unsentitems

--To list the failed items only:
USE msdb
SELECT sent_status, *
FROM sysmail_faileditems

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
EXEC sp_changedbowner [SA]

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
sp_configure 'show advanced options', 0
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!

Sunday, 4 November 2012

Troubleshooting Database Mail - Part I

I've been wrestling with Database Mail lately - working through a list of servers and instances, making sure that they all send emails in the event of a problem.  Most did, some didn't.  Some had never been set up; some had been set up to use an old mail server which was about to be decommissioned; and one was apparently set up perfectly - except that it didn't actually work  <sigh>.

My purpose in setting up database mail on a server is to send warning messages to myself and my team in case anything goes wrong.  If a backup job fails, for example, I want to know about it.  Some people set up a corresponding message for when a job succeeds - this is up to you, but my attitude is that things normally work, and I don't plan to worry unless they don't.  I usually run a job under supervision the first time, so I can see whether it runs or not.  

Let's start with setting Database Mail up from scratch.  This came in with SQL Server 2005, replacing the cleft stick and smoke signals used by earlier versions.  (What?  you still use SQL Server 2000?  I don't have personal experience, but this may help).  There's a perfectly good wizard for doing this, and in general I suggest that you follow the wizard.

Starting in SSMS then, expand Management by clicking on the + symbol.
Right click on Database Mail and select Configure Database Mail - up pops the wizard.

The Welcome screen is optional - if you don't want it, click on the Skip this page in the future box.  Press Next.

The default is best - Set up Database Mail by performing the following tasks...  The other options are really there for making changes and additions subsequently, although you probably could set things up from scratch this way.  But you are using the wizard because you want to do things the easy way, aren't you?  I certainly am.

Assuming this is your first time setting up Database mail, up pops this box:

Well, fairly obviously, Yes I do!  If not, you might as well give up now...

Here's the new profile dialog box:

Profile name - I usually use the server name - it's as good as any other.  And ditto for the description.  But if you are going to have several mail profiles per server, you'll need to give this a bit more thought.  

Then click on Add to add a new SMTP (outgoing mail) account,  

Again, use the server name for the account name and description, and also for the Display name.  The email address and company name need to be valid addresses, but you don't really want any emails being sent to them, hence noreply@...  My purpose in setting up database mail on a server is to send warning messages to myself and my colleagues in case anything goes wrong.  I don't then send a message back to the server - this only encourages the server gremlins.  

The bit that really matters here is the name of the outgoing mail server (SMTP).  Your network team will be able to tell you the name of the mail server, the port it uses (default is 25) and whether it requires a secure connection.  Notice the name:; not mail@... - it's a server name, not an email address.  

Click on OK and it takes you back to the New Profile screen, this time with the Account name added.  Not very exciting, but for completeness, here's what it looks like:

Now, click on Next.  This is the Manage Profile Security page:

Make the Profile public by ticking in the box.
Make it the default profile by selecting Yes from the pull down menu.  The pull-down doesn't show until you click in the box, but it's there, I promise.  
You can set up private profiles that can only be accessed by specific users.  But we don't need that here.

Click on Next and you get a chance to configure the system parameters.  Most of the time, the defaults are fine, so don't worry about it.

Click on Next and it gives you a chance to review the choices you have made:

Click on Finish and it does what you told it to do.  Look, you only have yourself to blame...  

Close, and test.  

The easiest way to test is by right clicking on Database Mail and selecting Send a Test Email.

It should have the profile name you entered before, although if you need several profiles you will need to use the pull down menu.  Put your own email address in the To: box, the subject and Body are fine (although you can change them if you want) and press Send.  Sit and twiddle your thumbs for a few moments and a test email should appear in your inbox.  

What if it doesn't?  What you want is Troubleshooting Database Mail - Part II
Stay tuned to this channel, database fans...