Service Broker by Database

Overview

The Service Broker by Database report is the instance view of the database level Service Broker page. It answers which databases on this instance have a disabled queue, undelivered messages or a pile of conversations that were never ended?

Each database is read with the same query and the same rules as the database page, so the counts and the findings agree on both.

A database is listed when its broker is enabled or when it has something in it: a user queue, a message waiting to be sent, or a conversation. Databases with the broker disabled and no queues are left out. The title says how many of the instance’s databases are listed.

Columns

Column Meaning
Database The database.
Status How many findings need a look and what the most severe is about, “No findings”, or why the database could not be read. Red when a finding is critical.
Broker Whether Service Broker is enabled in the database.
User Queues Queues created by users (system queues are not counted).
Disabled Queues Queues with RECEIVE or SEND turned off, by poison message handling or by hand. Red when any.
Queued Messages Messages in the database’s queues, from partition metadata.
Transmission Queue Messages sent from the database that have not been delivered.
Oldest Unsent How long the oldest of those has waited.
Open Conversations Conversation endpoints in every state but CLOSED.
Older Than 1 Day Open conversations that began more than a day ago.
Activated Tasks Activation procedures running now; “n/a” without VIEW SERVER STATE.

Using it

The databases are read one after another with a progress line and a Cancel button. A database that is offline or that the login cannot open is listed with the reason when its broker is enabled. Double click a row, or right-click and choose Open Service Broker, to open that database’s page with the queues, the transmission queue, the conversations and the fix scripts.

Azure SQL Database has no Service Broker; the page says so.

Report Why you would go there
Error Log Activation procedure failures and poison message events.
Email Alert Log Database Mail is built on Service Broker in msdb.
Waits BROKER_ waits across the instance.