Troubleshooting Database Mail on MSSQL

Now that Microsoft has replaced SQL Mail with Database Mail, it's easier to configure, simpler to configure, and also even easier to troubleshoot.
You just have to rely on a few queries that will give all the information needed:
1. sysmail_profile
2. sysmail_principalprofile
3. sysmail_account
4. sysmail_server
5. sysmail_servertype
6. sysmail_configuration
7. sysmail_sentitems
8. sysmail_unsentitems
9. sysmail_faileditems
10. sysmail_event_log
The views from 1 to 6 will give you all the configurations, and 7 to 10 will tell you what the problems are, and why.
MSSQL is very good at giving you the actually reason of the problem.

Database Mail only sends the emails to the mail server.
Database Mail does not deliver the email to the end user. That's the job of the mail server.
Only the mail server log will show you if the email has been delivered to the end user.

Applies to:

Microsoft SQL Server 2005

Database Mail configurations

Whenever, you have a problem with Database Mail, the first thing to do is to run the following queries.
Are these the settings that you expect?

PRINT '====>Profiles<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_profile;
go
PRINT '====>Email accounts<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_account;
go
PRINT '====>Mail server<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_server;
go
SELECT * FROM msdb.dbo.sysmail_servertype;
go
SELECT * FROM msdb.dbo.sysmail_configuration;
go

====>Profiles<====

profile_id  name                                                                                                                             description                                                                                                                                                                                                                                                      last_mod_datetime       last_mod_user
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
5           Main                                                                                                                             Main profile                                                                                                                                                                                                                                                     2007-09-17 08:44:32.123 SQLSERVER\froggy

(1 row(s) affected)

====>Email accounts<====

account_id  name                                                                                                                             description                                                                                                                                                                                                                                                      email_address                                                                                                                    display_name                                                                                                                     replyto_address                                                                                                                  last_mod_datetime       last_mod_user
----------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
6           System                                                                                                                           NULL                                                                                                                                                                                                                                                             XYZ@sqlhacks.com                                                                                                                sql                                                                                                                              NULL                                                                                                                             2007-09-17 08:44:32.123 SQLSERVER\froggy

(1 row(s) affected)

====>Mail server<====

account_id  servertype                                                                                                                       servername                                                                                                                       port        username                                                                                                                         credential_id use_default_credentials enable_ssl flags       last_mod_datetime       last_mod_user
----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ----------------------- ---------- ----------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
6           SMTP                                                                                                                             ZYX.ABC.com                                                                                                                      25          NULL                                                                                                                             NULL          0                       0          0           2007-09-17 08:44:32.123 SQLSERVER\froggy

(1 row(s) affected)

servertype                                                                                                                       is_incoming is_outgoing last_mod_datetime       last_mod_user
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
SMTP                                                                                                                             0           1           2005-10-14 01:55:32.850 sa

(1 row(s) affected)

paramname                                                                                                                                                                                                                                                        paramvalue                                                                                                                                                                                                                                                       description                                                                                                                                                                                                                                                      last_mod_datetime       last_mod_user
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
AccountRetryAttempts                                                                                                                                                                                                                                             1                                                                                                                                                                                                                                                                Number of retry attempts for a mail server                                                                                                                                                                                                                       2005-10-14 01:55:28.193 sa
AccountRetryDelay                                                                                                                                                                                                                                                60                                                                                                                                                                                                                                                               Delay between each retry attempt to mail server                                                                                                                                                                                                                  2005-10-14 01:55:28.193 sa
DatabaseMailExeMinimumLifeTime                                                                                                                                                                                                                                   600                                                                                                                                                                                                                                                              Minimum process lifetime in seconds                                                                                                                                                                                                                              2005-10-14 01:55:28.193 sa
DefaultAttachmentEncoding                                                                                                                                                                                                                                        MIME                                                                                                                                                                                                                                                             Default attachment encoding                                                                                                                                                                                                                                      2005-10-14 01:55:28.177 sa
LoggingLevel                                                                                                                                                                                                                                                     2                                                                                                                                                                                                                                                                Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3                                                                                                                                                                                     2005-10-14 01:55:28.193 sa
MaxFileSize                                                                                                                                                                                                                                                      1000000                                                                                                                                                                                                                                                          Default maximum file size                                                                                                                                                                                                                                        2005-10-14 01:55:28.193 sa
ProhibitedExtensions                                                                                                                                                                                                                                             exe,dll,vbs,js                                                                                                                                                                                                                                                   Extensions not allowed in outgoing mails                                                                                                                                                                                                                         2005-10-14 01:55:28.193 sa

(7 row(s) affected)

I have changed my real settings from ... to XYZ and ABC.

Emails status

MSSQL provides 4 views that shows the emails:
1. sysmail_allitems
2. sysmail_sentitems
3. sysmail_unsentitems
4. sysmail_faileditems

Here is the view for th failed items:

SELECT * FROM msdb.dbo.sysmail_faileditems;
go

mailitem_id profile_id  recipients                                                                                                                                                                                                                                                       copy_recipients                                                                                                                                                                                                                                                  blind_copy_recipients                                                                                                                                                                                                                                            subject                                                                                                                                                                                                                                                         body                                                                                                                                                                                                                                                             body_format          importance sensitivity  file_attachments                                                                                                                                                                                                                                                 attachment_encoding  query                                                                                                                                                                                                                                                            execute_query_database                                                                                                           attach_query_result_as_file query_result_header query_result_width query_result_separator exclude_query_output append_query_error send_request_date       send_request_user                                                                                                                sent_account_id sent_status sent_date               last_mod_date           last_mod_user
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------- ------------------- ------------------ ---------------------- -------------------- ------------------ ----------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------- ----------------------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
1           4           junk@sqlhacks.com                                                                                                                                                                                                                                                NULL                                                                                                                                                                                                                                                             NULL                                                                                                                                                                                                                                                             Blah Blah                                                                                                                                                                                                                                                       Artsi Fartsi...                                                                                                                                                                                                                                                  TEXT                 NORMAL     NORMAL       NULL                                                                                                                                                                                                                                                             MIME                 NULL                                                                                                                                                                                                                                                             NULL                                                                                                                             0                           1                   256                                       0                    0                  2007-09-17 08:38:52.187 SQLSERVER\froggy                                                                                                                 NULL            failed      2007-09-17 08:43:37.280 2007-09-17 08:43:37.280 SQLSERVER\froggy

(1 row(s) affected)

Why did the email fail?

MSSQL keeps tracks of all the mail events in the view: sysmail_event_log, including all the errors

SELECT SUBSTRING(fail.subject,1,25) AS 'Subject', 
       fail.mailitem_id, 
       LOG.description
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error';
go

Subject                   mailitem_id description
------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Blah Blah                 1           The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2007-09-17T08:39:00). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay 
Blah Blah                 1           The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2007-09-17T08:40:06). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay 

(2 row(s) affected)

The message from mail server is:
(Mailbox unavailable. The server response was: 5.7.1 Unable to relay
This happens when you configure the wrong server name, which is what happened in this case.
You must verify you setting with:

PRINT '====>Profiles<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_profile;
go
PRINT '====>Email accounts<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_account;
go
PRINT '====>Mail server<===='
PRINT ' '
SELECT * FROM msdb.dbo.sysmail_server;
go
SELECT * FROM msdb.dbo.sysmail_servertype;
go
SELECT * FROM msdb.dbo.sysmail_configuration;
go

Status of mail queues

To troubleshoot and see the status of the mail queues use:

USE msdb;
go
EXEC sysmail_help_queue_sp;
go

queue_type length      state                                                            last_empty_rowset_time  last_activated_time
---------- ----------- ---------------------------------------------------------------- ----------------------- -----------------------
mail       0           INACTIVE                                                         2007-09-17 16:04:53.873 2007-09-17 15:54:53.263
status     0           INACTIVE                                                         2007-09-17 15:54:53.903 2007-09-17 15:54:53.873

(2 row(s) affected)

The state of INACTIVE means that there is current emails in the queues.

Status of Database Mail

To troubleshoot and see the status of the Database Mail to see if it is started or not use:

USE msdb;
go
EXEC sysmail_help_status_sp;
go

Status
-------
STARTED

(1 row(s) affected)

STARTED means that Database Mail is started.