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.'
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:
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.
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,
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.
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.
Or as so often in SQL Server, there's another way to do it:
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.
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.
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.
-- 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
-- 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
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?
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!
