sysxmitqueue in msdb: Why It Grows and How to Clear It

Sysxmitqueue in msdb grows when Service Broker messages cannot leave the server. Clearing it means ending the conversations behind the messages. The table holds every message that waits to be sent.

The Symptom

A customer noticed that msdb had grown huge. Most calls about a big msdb involve the transaction log file. This time the data file used the space. A query that counted rows per table named the culprit. It was sys.sysxmitqueue, a table in msdb that holds Service Broker messages waiting to be sent. The large data file, rather than the log file, was the first clue.

The customer’s team had deployed a monitoring script earlier. The script used Service Broker, and its messages never reached their destination. The messages piled up in the table, and the table filled the file.

Where the Messages Wait

Service Broker sends a message from one service to another. If the target cannot be reached, the message waits in the transmission queue. The view sys.transmission_queue reads that queue, and sysxmitqueue is the table under it. Every waiting message is a row. A message waits when the target service does not exist. It also waits when a route points nowhere, or when the target database has Service Broker disabled.

The demo reproduces the pile in its own database. The script creates a sender service. It opens 20 conversations to a service that does not exist and sends 50 messages on each. Nothing in msdb changes.

IF DB_ID(N'TransmissionQueueDemo') IS NULL CREATE DATABASE TransmissionQueueDemo;
GO
USE TransmissionQueueDemo;
GO
IF EXISTS (SELECT 1 FROM sys.services WHERE name = N'//Demo/Sender') DROP SERVICE [//Demo/Sender];
IF OBJECT_ID(N'dbo.SenderQueue') IS NOT NULL DROP QUEUE dbo.SenderQueue;
IF EXISTS (SELECT 1 FROM sys.service_contracts WHERE name = N'//Demo/Contract') DROP CONTRACT [//Demo/Contract];
IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name = N'//Demo/Msg') DROP MESSAGE TYPE [//Demo/Msg];
GO
CREATE MESSAGE TYPE [//Demo/Msg] VALIDATION = NONE;
CREATE CONTRACT [//Demo/Contract] ([//Demo/Msg] SENT BY INITIATOR);
CREATE QUEUE dbo.SenderQueue;
CREATE SERVICE [//Demo/Sender] ON QUEUE dbo.SenderQueue;
GO
DECLARE @c int = 0, @m int, @h uniqueidentifier;
WHILE @c < 20
BEGIN
    BEGIN DIALOG CONVERSATION @h
        FROM SERVICE [//Demo/Sender] TO SERVICE N'//Demo/MissingTarget'
        ON CONTRACT [//Demo/Contract] WITH ENCRYPTION = OFF;
    SET @m = 0;
    WHILE @m < 50
    BEGIN
        SEND ON CONVERSATION @h MESSAGE TYPE [//Demo/Msg] (N'hello');
        SET @m += 1;
    END;
    SET @c += 1;
END;

The next queries count the waiting messages and read the reason. The column transmission_status explains why a message has not left.

SELECT COUNT(*) AS QueuedMessages, COUNT(DISTINCT conversation_handle) AS Conversations
FROM sys.transmission_queue;

SELECT TOP (1) transmission_status FROM sys.transmission_queue;

Find the Biggest Table

A row count shows the problem, but a size query shows it better. This one lists the three tables that reserve the most space in the current database. Run it in msdb when its data file is large.

SELECT TOP (3) OBJECT_NAME(ps.object_id) AS TableName,
       SUM(ps.row_count) AS RowsInTable,
       SUM(ps.reserved_page_count) * 8 AS ReservedKB
FROM sys.dm_db_partition_stats AS ps
WHERE ps.index_id IN (0, 1)
GROUP BY ps.object_id
ORDER BY ReservedKB DESC;

In the demo database, the 1,000 waiting messages already make sysxmitqueue the largest table. The second and third rows change with the version, so read only the first. In a bloated msdb, the first row is the problem.

Clear One Conversation

A conversation that cannot finish can be ended without a partner. END CONVERSATION ... WITH CLEANUP removes the endpoint and its waiting messages without telling the other side. Use it only for conversations that cannot complete. The next batch cleans one of the 20.

DECLARE @h uniqueidentifier;
SELECT TOP (1) @h = conversation_handle
FROM sys.conversation_endpoints
WHERE far_service = N'//Demo/MissingTarget';
END CONVERSATION @h WITH CLEANUP;
SELECT COUNT(*) AS QueuedMessages, COUNT(DISTINCT conversation_handle) AS Conversations
FROM sys.transmission_queue;

Clear Everything With NEW_BROKER

When nothing in the queue matters, one statement clears the lot. SET NEW_BROKER gives the database a new broker identity. Every conversation and every waiting message disappears without being delivered. The script below compares the identity before and after, then counts the messages.

DECLARE @before uniqueidentifier = (SELECT service_broker_guid FROM sys.databases WHERE name = N'TransmissionQueueDemo');
ALTER DATABASE TransmissionQueueDemo SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
SELECT CASE WHEN service_broker_guid = @before THEN N'Same' ELSE N'New' END AS BrokerId,
       (SELECT COUNT(*) FROM sys.transmission_queue) AS QueuedMessages
FROM sys.databases
WHERE name = N'TransmissionQueueDemo';

The Same Steps for msdb

In msdb, work in this order. Run the size query and confirm that sysxmitqueue in msdb is the largest table. Ask who deployed Service Broker code on the server. Take a full backup of msdb, because NEW_BROKER cannot be undone. Then clear the queue.

Database Mail also keeps queues in msdb, and the statement disconnects every session that uses msdb, including SQL Server Agent. Pick a quiet window, and test Database Mail and the Agent jobs afterward. The table empties, but the msdb data file keeps its size. Shrink the file afterward if you need the disk space.

-- first: BACKUP DATABASE msdb TO DISK = N'<path>' WITH CHECKSUM;
ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
-- no undo: restore the msdb backup to bring the discarded messages back

Clearing the table is only half of the job. If an application still sends messages that cannot arrive, the table fills again. Fix the sender. A dropped event notification stops new messages, but messages already queued stay until they are delivered or their conversations end. This read-only query lists the conversations that remain in msdb.

SELECT state_desc, COUNT(*) AS Endpoints
FROM msdb.sys.conversation_endpoints
GROUP BY state_desc;

The numbers depend on your server, so none are shown here. A large count in a state such as CONVERSING, with no application that needs it, points to conversations worth ending. MSDB Growth From queue_messages: How to Clear the Queue covers the neighboring case.

You could argue that NEW_BROKER is too blunt, and that ending conversations one at a time is safer. It is safer on a mixed workload. When the owner of the data confirms that nothing in the queue matters, one statement beats a loop.

What to Remember

A big sysxmitqueue in msdb means undelivered Service Broker messages. Find the biggest table first. Read the transmission status to learn why the messages wait. Clear them with CLEANUP for one conversation, or with NEW_BROKER for all of them.

Back up msdb before the clear, and fix the sender afterward. Run the cleanup script when you finish with the demo.

USE master;
GO
DROP DATABASE IF EXISTS TransmissionQueueDemo;

A full queue is not a storage problem, it is a delivery problem.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.