If you’re trying to send email using Database Mail in SQL Server, but it fails to send, you can check the sysmail_event_log view to see why it failed.

The sysmail_event_log view returns one row for each Windows or SQL Server message returned by the Database Mail system. By “message”, I don’t mean the actual mail message. I mean a message such as the error message that explains why the mail failed.

You can also use the sysmail_configure_sp stored procedure to determine what types of messages are logged.

Example

Here’s an example to demonstrate how to use sysmail_event_log to return all messages.

SELECT * FROM msdb.dbo.sysmail_event_log;

On my system this returns too much data to show here, but here it is again to show one message that’s returned.

SELECT * 
FROM msdb.dbo.sysmail_event_log
WHERE log_id = 2;

Result (using vertical output):

log_id        | 2
event_type    | error
log_date      | 2020-08-24 02:40:53.317
description   | The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-08-24T02:40:52). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 127.0.0.1:25).)
process_id    | 68
mailitem_id   | 1
account_id    | NULL
last_mod_date | 2020-08-24 02:40:53.317
last_mod_user | sa

I used vertical output in this example so that you don’t have to scroll sideways.

Check Your Logging Level

You can use the sysmail_help_configure_sp stored procedure to check what types of messages are logged.

Example:

EXEC msdb.dbo.sysmail_help_configure_sp 
    @parameter_name = LoggingLevel;

Result:

+--------------+--------------+------------------------------------------------------------------------------+
| paramname    | paramvalue   | description                                                                  |
|--------------+--------------+------------------------------------------------------------------------------|
| LoggingLevel | 2            | Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3 |
+--------------+--------------+------------------------------------------------------------------------------+

In my case, the LoggingLevel is 2, which is the default value. This setting logs errors, warnings, and informational messages.

Change Your Logging Level

You can use the sysmail_configure_sp stored procedure to change your logging level.

Specifically, you can choose a LoggingLevel of 1, 2, or 3.

These log the following:

1. Errors only.
2. Errors, warnings, and informational messages (default).
3. Errors, warnings, informational messages, success messages, and additional internal messages.

If you’re troubleshooting an issue, you might wish to temporarily change your logging level to 3.

Here’s an example of changing the logging level to 3.

EXECUTE msdb.dbo.sysmail_configure_sp  
    'LoggingLevel', '3';

After running that, I can check my LoggingLevel again with the sysmail_help_configure_sp stored procedure.

EXEC msdb.dbo.sysmail_help_configure_sp 
    @parameter_name = LoggingLevel;

Result:

+--------------+--------------+------------------------------------------------------------------------------+
| paramname    | paramvalue   | description                                                                  |
|--------------+--------------+------------------------------------------------------------------------------|
| LoggingLevel | 3            | Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3 |
+--------------+--------------+------------------------------------------------------------------------------+