Database Mail Setup and Troubleshooting

Step 1: Enable Database Mail

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

Step 2: Create the account and profile

EXEC msdb.dbo.sysmail_add_account_sp
     @account_name = N'DBA Mail',
     @email_address = N'[email protected]',
     @display_name = N'SQL Server - SERVERNAME',
     @mailserver_name = N'smtp.yourcompany.com',
     @port = 25;

EXEC msdb.dbo.sysmail_add_profile_sp
     @profile_name = N'DBA Mail Profile';

EXEC msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name = N'DBA Mail Profile',
     @account_name = N'DBA Mail',
     @sequence_number = 1;

Step 3: Send a test

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N'DBA Mail Profile',
     @recipients = N'[email protected]',
     @subject = N'Database Mail test',
     @body = N'Test message from SQL Server.';

Step 4: Troubleshoot

-- Recent mail and its status (sent, unsent, retrying, failed)
SELECT TOP (20) mailitem_id, recipients, subject, sent_status, send_request_date
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

-- Errors from the mail process
SELECT TOP (20) log_date, description
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC;

-- Is the mail queue running?
EXEC msdb.dbo.sysmail_help_status_sp;

-- Database Mail depends on Service Broker in msdb
SELECT is_broker_enabled FROM sys.databases WHERE name = N'msdb';
  • Mail stuck as unsent and status STOPPED: start it with EXEC msdb.dbo.sysmail_start_sp;
  • Mail failed: the event log usually shows the SMTP error. Common causes are the relay not accepting mail from this server’s IP address, authentication or TLS settings (@enable_ssl, @username, @password on the account), or a firewall blocking the port.
  • is_broker_enabled = 0 on msdb: this often happens after msdb has been restored. Enabling it needs exclusive access to msdb, so stop SQL Agent first (NEEDS APPROVAL): ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

From my SQL Server DBA handbook.