Service Broker

Overview

The Service Broker report answers is Service Broker in this database working, and if not, where are the messages stuck? Applications that use Service Broker, and the SQL Server features built on it (Database Mail, event notifications and query notifications), stop quietly when a queue is disabled or messages cannot be delivered. Nothing raises an error to the sender; the messages simply wait.

The report reads, in the selected database:

  • whether the broker is enabled (sys.databases.is_broker_enabled),
  • every queue (sys.service_queues) with its services, depth, activation settings and, with VIEW SERVER STATE, the queue monitor and the activation procedures running now,
  • the transmission queue (sys.transmission_queue) grouped by target service and status,
  • the conversation endpoints (sys.conversation_endpoints) counted by state and age,
  • the instance’s Service Broker endpoint and any routes to other instances.

Find it under the database in the tree: Real Time > Service Broker.

The top of the page

Status cards: Service Broker enabled or disabled, queues disabled (of all queues), messages in the queues, the transmission queue and the age of its oldest message, open conversations and how many are older than a day, activated tasks running, and the Service Broker endpoint. Click a card to open the grid view that lists what it counts.

Conversation endpoints by state: one bar split by state (CONVERSING, DISCONNECTED_INBOUND, CLOSED and so on). Click it to open the Conversations view.

Findings: the most severe first, colored by severity.

Findings

Finding Severity What it means
Queue disabled Critical RECEIVE or SEND is off on a queue. Poison message handling disables a queue after five rolled back RECEIVEs in a row, or someone ran ALTER QUEUE ... WITH STATUS = OFF. Messages pile up until it is turned back on.
Messages cannot be delivered Critical The transmission queue holds messages whose transmission_status reports an error (no route, unknown service, security or certificate errors). The status text is shown as SQL Server wrote it.
Messages waiting to be sent Warning Messages with no error that have waited more than 5 minutes. Check the route, the target broker and the endpoint.
Database Mail is stopped Warning In msdb, ExternalMailQueue is off because sysmail_stop_sp ran. Mail waits until sysmail_start_sp runs.
Service Broker disabled Warning The broker is off in a database that has user queues. Common on a restored or attached copy.
Activation procedure missing Warning Activation names a procedure that does not exist, so nothing reads the queue.
Activation notified but no reader running Warning The queue monitor started activation but no activated task is reading; the procedure is probably failing.
Queue backlog Warning 10,000 or more messages in one queue.
Leaked conversations Warning More than 10,000 open conversations began more than a day ago. Conversations that are never ended grow the database and tempdb.
Ended by the other side but not here Info, or Warning at 1,000 or more DISCONNECTED_INBOUND endpoints: the far side called END CONVERSATION and this side never did.
Conversations in the ERROR state Warning An error ended the conversation; the endpoint stays until this side ends it.
Remote routes but no started endpoint Warning A route points to another instance but the Service Broker endpoint is missing or stopped.
… could not be read Unknown A part of the read was refused. The queue monitors and activated tasks need VIEW SERVER STATE.

The grid

The toolbar switches the grid between four views. Every view supports Go to, CSV and Excel export.

View What it lists
Findings Each finding with its severity, area and detail. Fix Script says whether it has one.
Queues Every queue: state, services, depth, receive, enqueue, activation, activation procedure, max and current readers, monitor state, last activated, poison message handling and whether it is a system queue. A disabled queue is red.
Transmission Queue Messages waiting to leave this database, grouped by target service and transmission status, with the count and the oldest.
Conversations Endpoints by state: how many, how many began more than a day ago, how many have an unknown age, the oldest, and what the state means.

Double click a finding, a disabled queue or a conversation state, or right-click and choose Script …, to see the fix script. Scripts are shown, never run:

  • ALTER QUEUE ... WITH STATUS = ON, with the query to look at the message that kept failing first (otherwise poison message handling disables the queue again),
  • EXEC msdb.dbo.sysmail_start_sp for a stopped Database Mail,
  • ALTER DATABASE ... SET ENABLE_BROKER (and NEW_BROKER for a copy),
  • an END CONVERSATION ... WITH CLEANUP template, in batches of 10,000, with a warning: CLEANUP removes the conversation and its messages without telling the other side.

How it is measured

  • Queue depth comes from the row count of each queue’s internal table in sys.partitions, not from COUNT(*) on the queue, so a queue with millions of messages costs nothing to measure.
  • Conversation age: an endpoint has no creation date. A dialog begun with the default lifetime expires 2,147,483,647 seconds after it began, so that is where its age comes from. A dialog begun with a shorter LIFETIME is counted under Unknown Age.
  • Every part of the read runs inside TRY/CATCH, so a login without VIEW SERVER STATE still gets the queues, the transmission queue and the conversations, and the page says what it could not read. The read runs in the background with a progress bar and a Cancel button.
  • Azure SQL Database has no Service Broker; the page says so.
Report Why you would go there
Service Broker by Database The same checks for every database on the instance.
Error Log Activation procedure failures and poison message events are logged there.
Email Alert Log Database Mail is built on Service Broker in msdb.
Waits BROKER_ waits show where Service Broker is spending time.