Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server Database Mail not sending: read sysmail_event_log and the queue

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When SQL Server Database Mail does not send, the first job is to learn where the message stopped. It may have been refused by sp_send_dbmail itself, it may be waiting in a stopped or disabled queue, or the Database Mail external program may have reached the SMTP server and been turned away. Each leaves a different trace, and msdb holds all of them. Read the traces before changing a profile, an account or a firewall rule.

What the error means

Database Mail is a pipeline. sp_send_dbmail checks the request and queues it, a Service Broker queue in msdb starts the external program DatabaseMail.exe, and that program talks to the SMTP server. Microsoft states that when mail is placed in the queue, sp_send_dbmail returns the mailitem_id and the result "Mail queued."

The key point is that "Mail queued." only means the message reached the queue. Microsoft documents that sp_send_dbmail "still accepts mail when Database Mail is stopped using sysmail_stop_sp", and that when an error is returned from sp_send_dbmail, the message is not submitted to Database Mail and the error does not appear in sysmail_event_log. So there are two places to look: the error returned to the caller, and the msdb views for what happened after queuing.

Message status is one of four values: sent, unsent, retrying or failed. Version scope: these views and procedures are documented for SQL Server on Windows.

Likely causes

Cause What you see How to confirm
The caller is refused before queuing sp_send_dbmail returns an error, nothing appears in the views Read the error text returned to the caller
Database Mail is stopped, disabled or has no running queue Messages stay unsent and the event log has no recent entry sysmail_help_status_sp, sysmail_help_queue_sp, Database Mail XPs setting
Service Broker is off in msdb, or a queue is disabled Messages queue but no entries appear in sysmail_event_log or the Windows Application log is_broker_enabled for msdb
The SMTP server cannot be reached or rejects the message Status retrying or failed, with an error in sysmail_event_log Join sysmail_faileditems to sysmail_event_log
Authentication, port or TLS settings in the account are wrong Failed messages with an SMTP error description Account settings against what the SMTP server requires

Prove the cause before changing anything

These checks only read. Run them in msdb or with three-part names.

1. What did the caller get? Microsoft documents three refusals that happen before queuing. The first is Could not find stored procedure 'sp_send_dbmail', which means the call was not made from msdb and used no msdb.dbo. prefix. The second is Profile not valid: either the named profile does not exist, or the user has no permission to it. The third is EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. Sending needs membership in DatabaseMailUserRole in msdb. With no @profile_name, Microsoft says sp_send_dbmail uses the user's default private profile, then the default public profile, and returns an error if there is neither.

2. Is Database Mail running and is the queue moving?

EXECUTE msdb.dbo.sysmail_help_status_sp;
EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

The status is STARTED or STOPPED. For the queue, Microsoft documents the columns length, state (INACTIVE, NOTIFIED or RECEIVES_OCCURRING), last_empty_rowset_time and last_activated_time. A growing length with an old last_activated_time means nothing is picking mail up. An is_broker_enabled of 0 means Service Broker delivery is not activated in msdb.

3. Where is each message?

SELECT mailitem_id, recipients, subject, sent_status, send_request_date, sent_date
FROM msdb.dbo.sysmail_allitems
ORDER BY send_request_date DESC;

Microsoft documents sysmail_allitems as the superset of the sent, unsent and failed views. A status of unsent means the external program has not yet processed the message. Retrying means it tried and could not deliver, with network interruption, an SMTP server failure or an incorrect account as the reasons Microsoft lists. Sent means the SMTP server accepted it, so any further loss is on the SMTP side.

4. What did the external program log?

SELECT l.log_id, l.event_type, l.log_date, l.description, l.mailitem_id, l.account_id
FROM msdb.dbo.sysmail_event_log AS l
ORDER BY l.log_date DESC;

Microsoft recommends this view as the starting point. Rows with a NULL mailitem_id are not tied to one message, such as an external program failure. For one failed message, join sysmail_faileditems to sysmail_event_log on mailitem_id, as in Microsoft's sample. Expect a log entry that the external program started. If it is missing, Service Broker has not started it.

One trap: when delivery through one account fails and a later account succeeds, Microsoft says the accumulated errors are logged as separate warnings, so a warning does not prove the message was lost. If every account fails, the earlier warnings are logged as one error without an account_id.

5. Is verbose logging on? Microsoft says the external program by default does not log messages that were sent successfully. LoggingLevel has a default of 2 and a value of 3 for troubleshooting. If the log is thin, raise it temporarily (see the fix).

Fix

Stopped. Start the queues. The same msdb call is on Microsoft's common-errors page.

EXECUTE msdb.dbo.sysmail_start_sp;

Risk: queued mail starts to flow at once, including old messages. Microsoft notes that Service Broker checks the dialog lifetime, so messages that have waited longer than the configured lifetime fail immediately and are marked failed. You must decide whether to send them again. To back out, sysmail_stop_sp stops the queues.

Disabled by configuration. If Database Mail XPs is 0, Microsoft says it is off by default and that setting it to 1 takes effect immediately.

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Risk: this enables extended stored procedures that are off on a new installation to reduce the surface area. The last two lines put show advanced options back to 0, as Microsoft's own script does. Back out by setting Database Mail XPs to 0 the same way, which stops new starts but lets a running program finish when idle.

Service Broker off in msdb.

USE master;
GO
ALTER DATABASE msdb SET ENABLE_BROKER;
GO

Disruptive step: this changes a system database setting, and the statement can wait for other sessions to release locks on msdb. Do it in a maintenance window. Database Mail depends on the setting, so there is no reason to reverse it.

A disabled queue. Microsoft states a poison message, one that cannot be sent after five attempts, disables the queue, and re-enabling the queue does not help while the message remains. Microsoft's troubleshooting page says to check the activation_procedure column in msdb.sys.service_queues and read each procedure with sp_helptext, and adds that "it's better to compare the objects with a functioning Database Mail configuration": the message types, contract, queues and services it lists. Do not delete queue contents until you have made that comparison.

Permission or profile. Add the user to the role:

USE msdb;
GO
ALTER ROLE DatabaseMailUserRole ADD MEMBER [<database user>];
GO

Microsoft marks sp_addrolemember for removal in a future version and says to use ALTER ROLE instead, so this uses ALTER ROLE. The member must be a database user in msdb.

Disruptive step: this lets that principal send mail through every profile it can use. A new profile is private by default, and Microsoft says a private profile allows only sysadmin members until permission is granted. To back out, run ALTER ROLE DatabaseMailUserRole DROP MEMBER [<database user>]; in msdb. Use sysmail_help_principalprofile_sp to see profile access.

SMTP problems. In the Database Mail Configuration Wizard, check the server name and port, whether the server requires SSL or TLS, and the authentication mode. Microsoft lists three: Windows authentication using the Database Engine service account, basic authentication, and anonymous. Microsoft adds that basic authentication for Exchange Online is disabled in all tenants. To separate Database Mail from the server, Microsoft supplies a PowerShell SmtpClient test, which you run from the SQL Server host with the same server, port, SSL setting and credentials. If it fails too, the fault is on the SMTP side, and the mail relay and sender-authentication questions belong to that side.

Verbose logging. Raising the log level changes an instance-wide setting:

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

Risk: the log grows faster. Back out by setting it to 2, Microsoft's stated default.

Confirm the fix

Send one test message, note the returned mailitem_id, and watch it in sysmail_allitems until sent_status is sent. Confirm the recipient mailbox received it. Run sysmail_help_queue_sp again and check that length is falling.

Example (illustrative): on sql01, alerts leave at about 6 messages an hour. A maintenance script calls sysmail_stop_sp at 18:00 and never calls sysmail_start_sp. At 07:30 the next day the sp_send_dbmail calls still return "Mail queued.", and sysmail_help_status_sp returns STOPPED. That is 13.5 hours at 6 an hour, so about 81 messages are waiting (13.5 times 6). After sysmail_start_sp, the messages within the dialog lifetime go out and older ones show as failed. The administrator lists the failed rows and resends the two alerts that still matter.

What to watch afterwards

  • Alert when sysmail_help_status_sp returns STOPPED or sysmail_help_queue_sp shows a length that keeps rising.
  • Check sysmail_faileditems daily. Microsoft suggests benchmarking normal queue size so unusual backlogs are visible.
  • Delete old unsent messages only when they are the oldest in the queue, with msdb.dbo.sysmail_delete_mailitems_sp, as Microsoft's note advises. Disruptive step: the deleted items cannot be recovered.
  • Keep sender settings and SMTP credentials in your runbook.

Risks and limits

These commands have not been run or lab-tested. Microsoft's pages describe the pipeline and its views but give no list of SMTP error texts, so read the SMTP description in sysmail_event_log against the SMTP server's own documentation. Changes to msdb, Service Broker and server configuration options affect every application that sends mail from the instance.

Sources