Service Broker Not Enabled for MSDB

What this check looks for

sys.databases where name = 'msdb' and is_broker_enabled is not 1.

Why it matters

Database Mail is a Service Broker application. It lives in msdb, and with the broker disabled there it accepts messages and never sends them.

That is the whole significance of this finding, and it is worth spelling out because the symptom is so misleading. When you call sp_send_dbmail:

• The procedure succeeds. It returns a mail item id.
• The message is queued in msdb.
• The external mail process is never activated, because activation is a Service Broker mechanism.
• The message sits in the queue with a status of unsent, indefinitely.

No error is raised anywhere. Not to the caller, not in the error log, not in the Database Mail event log, because nothing failed. Something simply never started.

So every diagnostic points the wrong way. The profile looks correct. The account looks correct. sysmail_help_status_sp may even report the queue as started. A test message returns success. And nothing arrives, which sends people to the mail server and the firewall, where there is nothing to find.

What stops working as a result:

• Every alert on severity 19 through 25 and on corruption errors 823, 824 and 825.
• Every job failure notification, including backups and integrity checks.
• Anything your own code sends with sp_send_dbmail.

Other things in msdb use the broker too, including some Agent internals and external activation, but Database Mail is the one that matters on most instances.

The usual cause is a restore or a move. Restoring msdb, or attaching it, gives it a new broker GUID and the broker ends up disabled. It also happens after some in-place upgrades.

How to confirm it yourself

SELECT [name], [is_broker_enabled], [service_broker_guid], [is_honor_broker_priority_on]
  FROM sys.databases WITH (NOLOCK)
 WHERE [name] = 'msdb';

The evidence that it is actually causing the symptom, which is mail sitting unsent:

SELECT [sent_status], COUNT(*) AS [messages],
       MIN([send_request_date]) AS [oldest], MAX([send_request_date]) AS [newest]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 GROUP BY [sent_status];

A pile of unsent messages with no errors in the event log is this finding’s signature. Failed or retrying messages mean something different, and that has its own check.

SELECT TOP (20) [log_date], [event_type], [description]
  FROM msdb.dbo.sysmail_event_log WITH (NOLOCK)
 ORDER BY [log_date] DESC;

An empty or stale event log alongside queued messages is the confirmation: nothing has even attempted to send.

And the queue itself:

SELECT [name], [is_receive_enabled], [is_activation_enabled], [is_enqueue_enabled]
  FROM msdb.sys.service_queues WITH (NOLOCK)
 WHERE [name] LIKE 'ExternalMailQueue%';

How to fix it

Enabling the broker on msdb needs exclusive access, which means stopping SQL Server Agent first, because Agent holds a connection to msdb continuously.

-- 1. stop SQL Server Agent, from Configuration Manager or:
--    net stop SQLSERVERAGENT

-- 2. enable the broker
ALTER DATABASE [msdb] SET ENABLE_BROKER;

-- 3. start Agent again
--    net start SQLSERVERAGENT

If ALTER DATABASE blocks, something still holds a connection. Find it rather than guessing:

SELECT [session_id], [login_name], [host_name], [program_name], [status]
  FROM sys.dm_exec_sessions WITH (NOLOCK)
 WHERE [database_id] = DB_ID('msdb');

SET NEW_BROKER is the other option and it is not the same thing. It assigns a new broker GUID and discards everything currently in the queues, which for msdb means throwing away any queued mail. Use ENABLE_BROKER unless the broker GUID is genuinely conflicting with another instance, which happens when msdb was restored from a server that is still running.

Then confirm mail actually flows:

EXEC msdb.dbo.sysmail_start_sp;

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N'YourProfile',
     @recipients   = N'[email protected]',
     @subject      = N'Broker test',
     @body         = N'If this arrives, the broker is working.';

SELECT TOP (5) [sent_status], [send_request_date], [sent_date], [subject]
  FROM msdb.dbo.sysmail_allitems WITH (NOLOCK)
 ORDER BY [mailitem_id] DESC;

And deal with the backlog. Messages queued while the broker was off may now send all at once, which can be a surprise if it has been off for months. Reviewing and clearing old unsent items before starting is often the kinder option.

How long it takes

About two hours, nearly all of it arranging a moment when Agent can be stopped.

Related reports

Related checks

Frequently asked questions

Database Mail says the queue is started. Is the broker still the problem? Possibly. The queue can report as started while activation does not occur, which is why the unsent count is the better signal.

Why does enabling it need Agent stopped? ALTER DATABASE ... SET ENABLE_BROKER needs exclusive access to msdb, and Agent holds a connection to it for as long as it runs.

Should I use NEW_BROKER instead? Only if the broker GUID conflicts with another instance, typically because msdb was restored from a server that is still running. It discards queued messages, so it is not the default choice.

We do not use Database Mail. Then the impact is small today. It is worth enabling anyway, because the first alert you configure will otherwise go nowhere with no error.