Quick Scan Report – Database Mail Not Configured

What this check looks for

sys.configurations where Database Mail XPs has a value_in_use of 0, meaning the feature is switched off, and the related condition where it is enabled but no profile is configured. Express edition is excluded, because it has no SQL Server Agent to notify with.

Why it matters

Database Mail is the only mechanism SQL Server has for sending email. With it off, every notification path on the instance terminates in nothing.

The things that stop working are exactly the ones you would want most:

  • Alerts on severity 19 through 25, and on the corruption errors 823, 824 and 825.
  • Job failure notifications, which includes backup and integrity check jobs.
  • Anything your own code sends with sp_send_dbmail, such as an overnight report or a monitoring script.

The reason this sits at Medium rather than High is that it is a dependency rather than a symptom. On its own it breaks nothing. What it does is guarantee that several other findings on this report cannot be fixed until it is dealt with: configuring an operator, wiring job notification, or adding alerts all end at an address nothing can send to.

So it is worth doing first. It is an hour of work, it needs no downtime, and it unblocks three or four other checks.

How to confirm it yourself

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('Database Mail XPs', 'show advanced options');

Whether anything is configured, even if the feature is on:

EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_status_sp;      -- STARTED or STOPPED

A profile with no account, or an account with no profile, is as useless as the feature being off, and both are common half-finished states.

And whether Agent knows which profile to use, which is a separate setting people miss:

EXEC msdb.dbo.sp_get_sqlagent_properties;

How to fix it

Four steps, in order. Skipping any one of them leaves it not working.

1. Enable the feature:

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

2. Create an account, which is the SMTP server details:

EXEC msdb.dbo.sysmail_add_account_sp
     @account_name    = N'SQLMail',
     @email_address   = N'[email protected]',
     @display_name    = N'SQL Server on PRODSQL01',
     @mailserver_name = N'smtp.yourcompany.com',
     @port            = 25;

Use a display name that says which server it is. When an alert arrives at 3am, “SQL Server” is not enough to act on.

3. Create a profile and attach the account:

EXEC msdb.dbo.sysmail_add_profile_sp
     @profile_name = N'DBAProfile',
     @description  = N'Profile for alerts and job notifications';

EXEC msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name  = N'DBAProfile',
     @account_name  = N'SQLMail',
     @sequence_number = 1;

EXEC msdb.dbo.sysmail_add_principalprofile_sp
     @profile_name = N'DBAProfile',
     @principal_name = N'public',
     @is_default = 1;

4. Tell SQL Server Agent to use it. This is the step most often missed, and without it the profile exists and Agent still sends nothing. In SQL Server Agent properties, on the Alert System page, tick “Enable mail profile” and select the profile. Agent must be restarted for that to take effect.

Then test it, and test it end to end:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N'DBAProfile',
     @recipients   = N'[email protected]',
     @subject      = N'Database Mail test',
     @body         = N'If this arrives, Database Mail works.';

-- and confirm it actually left rather than queuing
SELECT TOP (5) [sent_status], [send_request_date], [sent_date], [subject]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 ORDER BY [mailitem_id] DESC;

The usual reason a test fails is the mail server refusing to relay for this host. That is a conversation with whoever runs the mail system, and the error text in msdb.dbo.sysmail_event_log names it.

Then go and finish the job: create an operator, wire the alerts, and set job failure notification. Each has its own check on this report and none of them works until this one does.

How long it takes

About an hour, most of it getting the SMTP relay details and permission to send.

Report Why you would go there
Database Mail Setup Profiles, accounts and what they point at.
Database Mail History Whether anything has been sent.
Alerts and Operators What will use it once it works.
Agent Settings Whether Agent has been told which profile to use.
Email Alert Log Delivery from the alerting side.
Check
No Operators Configured The address mail is sent to.
Missing Alerts The alerts that need delivering.
Agent Jobs without failure notification email Job failures that need delivering.
Failed Database Mail Once it is on, whether messages are arriving.
Database Mail Agent Configuration Agent’s own mail setting specifically.

Frequently asked questions

We use external monitoring, so we do not need it. Reasonable, provided the external tool watches job outcomes and the error log severities. It is still worth having for anything that calls sp_send_dbmail directly.

Is SQL Mail the same thing? No. SQL Mail was the old MAPI based feature and it has been removed. Database Mail is the supported replacement and does not need Outlook installed.

Do we need a mailbox for the server? Not necessarily a mailbox, but the SMTP relay has to accept mail from this host and from the address you configure. That is usually the whole of the setup difficulty.

Why does Agent need a separate setting? Because Agent has its own mail configuration that points at a Database Mail profile. Enabling the feature and creating a profile does not tell Agent to use it, and Agent needs a restart after the change.