Service Broker is a powerful feature in SQL Server that allows you to build asynchronous, reliable, and scalable applications. However, if not properly configured, it can lead to issues such as undeliverable messages and a bloated msdb database.

One common problem that DBAs may encounter is a growing sysxmitqueue table in the msdb database. The sysxmitqueue table is a queue table for Service Broker messages, and if it starts to grow, it indicates that there are undeliverable messages. This usually means that Service Broker is misconfigured in one or more databases involved in the messaging process.

To identify the issue, you can query the sys.transmission_queue system catalog view and check the transmission_status column. If you receive a message like “The Service Broker in the target database is unavailable: ‘The service broker is administratively disabled.'”, it confirms that Service Broker is not properly enabled in at least one of the databases.

To verify the Service Broker configuration in each database, you can run the following query:

SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID();

If you determine that Service Broker is not needed or not properly enabled in a particular database, you can consider removing the components associated with the failed messages. However, it is important to consult with relevant stakeholders before making any changes.

In the meantime, you can clear out some of the undeliverable messages by running the following code:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

Here, ‘conversation_handle’ refers to the handle retrieved from the sys.transmission_queue system catalog view. After executing this code, you should see a significant reduction in the number of rows in the sysxmitqueue table.

It is worth noting that although the rows may be reduced, the space occupied by the table may not be immediately released. This behavior is similar to purging maintenance plan logs, as discussed in a previous article.

In the upcoming posts, we will delve further into maintaining the msdb database and discuss how to release the space occupied by the sysxmitqueue table.