Applies toSQL Server 2016–2025 (Azure SQL Managed Instance uses a fixed profile name for Agent; check current docs)
Symptom
sp_send_dbmailand the Database Mail test email work.- Jobs are set to email an operator on failure, but nothing arrives.
msdb.dbo.sysmail_allitemshas no rows for the job notifications.- The Agent log (
SQLAGENT.OUT) may say[264] An attempt was made to send an email when no email session has been established.
No row in sysmail_allitems means Agent never handed the email to Database Mail.
Fix in SSMS
- Object Explorer → SQL Server Agent → right-click → Properties.
- Alert System page.
- Tick Enable mail profile. Mail system: Database Mail. Mail profile: pick your profile.
- Optionally set a fail-safe operator on the same page.
- OK, then restart SQL Server Agent. The setting is read only at startup.
Fix in T-SQL
The Agent properties dialog writes these registry values; the same can be scripted:
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'UseDatabaseMail', N'REG_DWORD', 1;
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile', N'REG_SZ', N'DBA Mail';
Then restart Agent:
Restart-Service -Name SQLSERVERAGENT # default instance
# Restart-Service -Name 'SQLAgent$INST01' # named instance
xp_instance_regwrite is undocumented but is what SSMS itself scripts. Use it in build scripts; check it with the SSMS dialog afterwards.
Other checks if it still doesn't work
| Check | Query / place |
|---|---|
| Operator exists, enabled, correct address | SELECT name, enabled, email_address FROM msdb.dbo.sysoperators; |
| Job really notifies on failure | SELECT name, notify_level_email, notify_email_operator_id FROM msdb.dbo.sysjobs; |
| Profile is accessible to Agent | Database Mail profile is public or the Agent account has access to it |
| Database Mail running | EXEC msdb.dbo.sysmail_help_status_sp; (STARTED); start with sysmail_start_sp |
| Mail queued but not sent | SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_id DESC; for SMTP errors |
| Database Mail XPs enabled | EXEC sp_configure 'Database Mail XPs'; should be 1 |
Verify end to end
- Create a test job with one step
RAISERROR('Mail path test', 16, 1);. - Set it to email
DBA Teamon failure, run it. - Within a minute there should be a row in
sysmail_allitemswithsent_status = 'sent', and an email in the inbox. - Delete the test job.
Warning
After moving to a new server or rebuilding Agent, this setting is easy to miss. Database Mail is configured in msdb, but the Agent mail profile is in the registry, so it doesn't move with msdb.
Prevent it next time
- Include the two registry settings and an Agent restart in your build script.
- Monthly test job that fails on purpose and must produce an email.
- Monitor
sysmail_event_logfor errors.