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.