Part of the DBA-Tools Project.
Two Messaging Systems That Run Quietly Until They Don’t
Service Broker and Database Mail are both messaging infrastructure, and both are easy to forget are even active, since SQL Server uses Service Broker internally for things like Availability Group health checks, and Database Mail just sits there until something needs to send an alert. Neither gets much attention until a message gets stuck.
Service Broker Health checks every database for accumulating disconnected conversation endpoints and transmission queue backlogs, the two ways Service Broker degrades silently over time. Database Mail Queue checks for failed, retrying, or unsent mail items, the difference between “the alert was configured” and “the alert actually arrived.”
Why Service Broker Health and Database Mail Queue Matter
- Disconnected Service Broker conversation endpoints accumulate silently, a few thousand is a real, measurable resource cost that nobody notices until it’s tens of thousands
- Service Broker is implicitly active on many instances even without deliberate application use, Database Mail and Availability Group health checks both use it internally
- A failed alert email is often discovered only when someone asks “why didn’t I get paged,” well after the alert was needed
- Both checks are the difference between “this is configured” and “this is actually working,” which is the real question that matters operationally
When to Run These Scripts
- Service Broker Health: routine health checks on any instance, especially one running Availability Groups (which use Service Broker internally for health checks)
- Database Mail Queue: any time an expected alert email didn’t arrive, or as a routine check that alerting infrastructure is actually functional
- After enabling a new feature that uses Service Broker (Database Mail, Query Notifications, a custom messaging application)
- Before troubleshooting “the job succeeded but nobody got notified,” Database Mail Queue is the fastest way to confirm whether the message even sent
The Scripts
Get-ServiceBrokerHealth — Endpoints and Queues Across Every Database
/*
Script Name : Get-ServiceBrokerHealth
Category : monitoring
Purpose : Service Broker health across all user databases. Orphaned/disconnected
conversation endpoints accumulate silently over months, eventually degrading
SB infrastructure. SB is implicitly active on many instances (Database Mail,
AG health checks use it). Surfaces conversation endpoint counts by state,
transmission queue depth, and queue activation status.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-service-broker-health-and-database-mail-queue/)
Requires : VIEW ANY DATABASE, VIEW DATABASE STATE
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-service-broker-health-and-database-mail-queue/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
CREATE TABLE #sb_health (
database_name SYSNAME,
sb_enabled BIT,
total_endpoints INT,
endpoints_active INT,
endpoints_error INT,
endpoints_disconnected INT,
endpoints_closed_stale INT,
transmission_queue_total INT,
transmission_queue_errors INT,
queues_active INT,
queues_disabled INT,
activated_tasks_running INT,
status NVARCHAR(500)
);
DECLARE @db SYSNAME;
DECLARE @sql NVARCHAR(MAX);
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'
INSERT INTO #sb_health
SELECT
N' + QUOTENAME(@db, N'''') + N',
d.is_broker_enabled,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.conversation_endpoints) AS total_endpoints,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.conversation_endpoints
WHERE state_desc IN (''STARTED_OUTBOUND'',''STARTED_INBOUND'',''CONVERSING'')) AS endpoints_active,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.conversation_endpoints
WHERE state_desc LIKE ''%ERROR%'') AS endpoints_error,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.conversation_endpoints
WHERE state_desc LIKE ''DISCONNECTED%'') AS endpoints_disconnected,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.conversation_endpoints
WHERE state_desc = ''CLOSED'' AND DATEDIFF(DAY, lifetime, GETDATE()) > 0) AS endpoints_closed_stale,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.transmission_queue) AS transmission_queue_total,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.transmission_queue
WHERE transmission_status IS NOT NULL AND transmission_status <> '''') AS transmission_queue_errors,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.service_queues WHERE is_receive_enabled = 1) AS queues_active,
(SELECT COUNT(*) FROM ' + QUOTENAME(@db) + N'.sys.service_queues WHERE is_receive_enabled = 0) AS queues_disabled,
(SELECT COUNT(*) FROM sys.dm_broker_activated_tasks t
WHERE t.database_id = DB_ID(N' + QUOTENAME(@db, N'''') + N')) AS activated_tasks_running,
''OK''
FROM sys.databases d
WHERE d.name = N' + QUOTENAME(@db, N'''') + N';';
BEGIN TRY EXEC sp_executesql @sql; END TRY
BEGIN CATCH END CATCH;
FETCH NEXT FROM db_cursor INTO @db;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
UPDATE #sb_health SET status =
CASE
WHEN sb_enabled = 0 THEN 'INFO — Service Broker disabled on this database'
WHEN endpoints_disconnected > 10000
THEN 'CRITICAL — ' + CAST(endpoints_disconnected AS VARCHAR) +
' disconnected endpoints; run END CONVERSATION ... WITH CLEANUP to reclaim resources'
WHEN endpoints_disconnected > 1000
THEN 'WARN — ' + CAST(endpoints_disconnected AS VARCHAR) + ' disconnected endpoints accumulating; schedule cleanup'
WHEN endpoints_error > 100
THEN 'WARN — ' + CAST(endpoints_error AS VARCHAR) + ' conversation endpoints in ERROR state'
WHEN transmission_queue_errors > 0
THEN 'WARN — ' + CAST(transmission_queue_errors AS VARCHAR) + ' messages stuck in transmission queue with delivery errors'
WHEN transmission_queue_total > 1000
THEN 'WARN — ' + CAST(transmission_queue_total AS VARCHAR) + ' messages queued for transmission; check SB connectivity and activation'
ELSE 'OK'
END;
SELECT * FROM #sb_health
ORDER BY
CASE WHEN status LIKE 'CRITICAL%' THEN 1
WHEN status LIKE 'WARN%' THEN 2
WHEN status LIKE 'INFO%' THEN 3
ELSE 4 END,
database_name;
DROP TABLE #sb_health;
Get-DatabaseMailQueue — Did the Alert Actually Send
/*
Script Name : Get-DatabaseMailQueue
Category : monitoring
Purpose : Database Mail items that are failed, retrying, or unsent — plus last 24 hours of sent mail for context. Shows error detail for failed items.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-service-broker-health-and-database-mail-queue/)
Requires : msdb access (DatabaseMailUserRole or sysadmin)
*/
-- Blog: https://sqldba.blog/dba-scripts-get-service-broker-health-and-database-mail-queue/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
m.mailitem_id,
m.subject,
LEFT(m.recipients, 200) AS recipients,
m.sent_status,
m.send_request_date,
m.sent_date,
DATEDIFF(MINUTE, m.send_request_date, COALESCE(m.sent_date, GETDATE())) AS queue_minutes,
m.send_request_user,
LEFT(l.description, 500) AS error_detail
FROM msdb.dbo.sysmail_allitems m
LEFT JOIN (
SELECT mailitem_id, MAX(log_id) AS latest_log_id
FROM msdb.dbo.sysmail_event_log
WHERE event_type = 'error'
GROUP BY mailitem_id
) latest ON latest.mailitem_id = m.mailitem_id
LEFT JOIN msdb.dbo.sysmail_event_log l ON l.log_id = latest.latest_log_id
WHERE m.sent_status IN ('failed', 'retrying', 'unsent')
OR m.send_request_date >= DATEADD(HOUR, -24, GETDATE())
ORDER BY
CASE m.sent_status WHEN 'failed' THEN 1
WHEN 'retrying' THEN 2
WHEN 'unsent' THEN 3
ELSE 4 END,
m.send_request_date DESC;
How To Run From The Repo
Clone DBA Tools, initialize and run either script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Service Broker health across every database:
.\run.ps1 Get-ServiceBrokerHealth
# Failed, retrying, or unsent Database Mail items:
.\run.ps1 Get-DatabaseMailQueue
# To run against a remote sql server:
.\run.ps1 Get-ServiceBrokerHealth -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/monitoring/features/Get-ServiceBrokerHealth.sqlsql/monitoring/features/Get-DatabaseMailQueue.sql
Example Output
Real output from this lab instance, not staged. Get-ServiceBrokerHealth across 20 databases: Service Broker is enabled everywhere (sb_enabled = True), but every database shows total_endpoints = 0, a genuinely clean, unused-but-enabled state, status = OK throughout. Get-DatabaseMailQueue returns zero rows, no mail sent, failed, retrying, or unsent in the last 24 hours, honest and expected since Database Mail has never been exercised on this lab box.
On an instance with real Service Broker traffic, a row looks like this:
| database_name | sb_enabled | endpoints_disconnected | status |
|---|---|---|---|
| OrdersDB | True | 14203 | CRITICAL — 14203 disconnected endpoints; run END CONVERSATION … WITH CLEANUP to reclaim resources |
Understanding the Results
Service Broker Health:
– status = CRITICAL (endpoints_disconnected > 10000) — a real, accumulated resource cost; END CONVERSATION ... WITH CLEANUP reclaims these, run it deliberately, not as a routine automated job without understanding why the backlog built up
– transmission_queue_errors > 0 — messages are stuck failing delivery, worth checking connectivity and whether the receiving queue’s activation procedure is actually running
– sb_enabled = True with total_endpoints = 0 — enabled but genuinely unused, not a problem, just confirms the feature isn’t actively doing anything on this database
Database Mail Queue:
– sent_status = failed with error_detail populated — the specific reason is usually actionable directly (SMTP auth failure, relay rejected, etc.), read it before assuming the mail profile itself is broken
– sent_status = retrying — still in progress, but worth checking whether it’s been retrying for an unreasonably long time
– Zero rows — no mail activity at all in the last 24 hours; on an instance expected to be sending routine alerts, this itself might be the finding
Best Practices
- Run Service Broker Health on any AG-enabled instance as a routine check, since AG health checks use Service Broker internally, an SB problem can quietly affect AG monitoring itself
- Clean up disconnected conversation endpoints deliberately once identified, don’t just note the number and move on, it’s a real, growing resource cost
- Treat a missing expected alert email as a two-step check: first Database Mail Queue (did it even try to send), then the actual SMTP/relay configuration if the queue shows nothing wrong
- Re-run both periodically even without a known problem, both degrade quietly with no proactive alerting of their own
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Why is Service Broker enabled on databases that don’t use it directly?
is_broker_enabled is a database-level setting that’s on by default for new databases in modern SQL Server versions, regardless of whether the database’s own application uses Service Broker features. Availability Groups and Database Mail both rely on Service Broker internally, which is part of why it shows enabled so broadly even on databases without custom messaging.
What actually causes disconnected conversation endpoints to accumulate?
Usually applications that begin a Service Broker conversation and don’t explicitly end it, or a receiving service that stops running (activation procedure error, queue disabled) while senders keep opening new conversations. The fix is ending the accumulated conversations with cleanup, and then fixing whatever stopped closing them properly going forward.
Summary
Service Broker and Database Mail are both messaging systems that work silently until they don’t, and neither alerts you proactively when something’s wrong. Service Broker Health catches the resource drain of accumulating disconnected endpoints before it becomes a real problem. Database Mail Queue answers the specific, practical question that matters when an expected alert doesn’t arrive: did it even try to send.
Run both as routine checks, especially on any AG-enabled instance, and treat a growing disconnected-endpoint count or a failed mail item as worth acting on the same day it’s found.
Leave a Reply