MSDB Growth From queue_messages: How to Clear the Queue

MSDB growth from the queue_messages tables means a Service Broker queue holds messages that nobody reads. The fix is to end those conversations, and only those. A script that ends every conversation can break Database Mail.

What Causes MSDB Growth

The msdb database uses Service Broker for several features. Database Mail has an internal and an external queue. Policy based management has its own queue, and so do event notifications. Each queue stores its messages in a hidden table named queue_messages_ followed by the object ID of the queue. When messages arrive and nothing reads them, that table grows, and so does msdb.

A Queue With Nobody Listening

To see the problem safely, the demo builds its own queue in a test database. It creates two services and a queue for each. The initiator sends messages to the target, and nobody ever reads the target queue.

IF DB_ID(N'BrokerQueueDemo') IS NULL CREATE DATABASE BrokerQueueDemo;
GO
USE BrokerQueueDemo;
GO
CREATE MESSAGE TYPE [//Demo/Note] VALIDATION = NONE;
CREATE CONTRACT [//Demo/NoteContract] ([//Demo/Note] SENT BY INITIATOR);
CREATE QUEUE dbo.TargetQueue;
CREATE QUEUE dbo.InitiatorQueue;
CREATE SERVICE [//Demo/Target] ON QUEUE dbo.TargetQueue ([//Demo/NoteContract]);
CREATE SERVICE [//Demo/Initiator] ON QUEUE dbo.InitiatorQueue;

Now send 2,000 messages, each on its own conversation. Each message is 2,000 characters long.

SET NOCOUNT ON;
DECLARE @h uniqueidentifier, @i int = 0, @body nvarchar(max) = REPLICATE(CAST(N'y' AS nvarchar(max)), 2000);
WHILE @i < 2000
BEGIN
    BEGIN DIALOG CONVERSATION @h FROM SERVICE [//Demo/Initiator] TO SERVICE N'//Demo/Target' ON CONTRACT [//Demo/NoteContract] WITH ENCRYPTION = OFF;
    SEND ON CONVERSATION @h MESSAGE TYPE [//Demo/Note] (@body);
    SET @i += 1;
END;

Find the Big Queue

This query lists every queue of the current database with the space its hidden table uses. It also shows whether the queue can be read and whether activation is on. Activation is the setting that starts a procedure to read the queue.

SELECT q.name AS QueueName, q.is_receive_enabled, q.is_activation_enabled, SUM(ps.used_page_count) * 8 AS UsedKB
FROM sys.service_queues q
JOIN sys.internal_tables it ON it.parent_object_id = q.object_id AND it.internal_type_desc = N'QUEUE_MESSAGES'
JOIN sys.dm_db_partition_stats ps ON ps.object_id = it.object_id
GROUP BY q.name, q.is_receive_enabled, q.is_activation_enabled
ORDER BY UsedKB DESC;

TargetQueue is the one that grows. Run the same query in msdb to find your own culprit. On this test instance every msdb queue is empty, and the largest uses 40 KB.

USE msdb;
GO
SELECT q.name AS QueueName, q.is_receive_enabled, q.is_activation_enabled, SUM(ps.used_page_count) * 8 AS UsedKB
FROM sys.service_queues q
JOIN sys.internal_tables it ON it.parent_object_id = q.object_id AND it.internal_type_desc = N'QUEUE_MESSAGES'
JOIN sys.dm_db_partition_stats ps ON ps.object_id = it.object_id
GROUP BY q.name, q.is_receive_enabled, q.is_activation_enabled
ORDER BY UsedKB DESC;

Look Before You Clean

Messages belong to conversations, and every conversation has two endpoints, one on each side. The next query counts the endpoints for each service. It shows who the messages belong to. far_service is the service at the other end of the endpoint.

USE BrokerQueueDemo;
GO
SELECT far_service, is_initiator, state_desc, COUNT(*) AS Endpoints
FROM sys.conversation_endpoints
GROUP BY far_service, is_initiator, state_desc
ORDER BY far_service;
SELECT COUNT(*) AS MessagesWaiting FROM dbo.TargetQueue;
SELECT size / 128 AS FileMB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128 AS UsedMB FROM sys.database_files WHERE type_desc = N'ROWS';

The demo has 2,000 conversations, so 4,000 endpoints and 2,000 waiting messages. The file holds 21 MB of data. In msdb, you’d see the services of Database Mail and other features next to your own. Some scripts end every conversation in the database. That ends the conversations Database Mail still uses. Look at the names first, and clean only the ones you own.

Clear Only Your Conversations

END CONVERSATION ... WITH CLEANUP removes an endpoint and its messages at once, without telling the other side. That’s right for a conversation that nobody will ever finish. The loop below takes one endpoint at a time, filtered by service name, and cleans it. Take a backup of msdb first if this is msdb. Run the loop there only after the far_service names show that you own the service.

SET NOCOUNT ON;
DECLARE @h uniqueidentifier, @ended int = 0;
WHILE 1 = 1
BEGIN
    SELECT TOP (1) @h = conversation_handle FROM sys.conversation_endpoints WHERE far_service IN (N'//Demo/Initiator', N'//Demo/Target');
    IF @@ROWCOUNT = 0 BREAK;
    END CONVERSATION @h WITH CLEANUP;
    SET @ended += 1;
END;
SELECT @ended AS EndpointsCleaned;
SELECT COUNT(*) AS MessagesWaiting FROM dbo.TargetQueue;

The queue is empty. On a large queue the loop takes a while. Run it in the background, or in batches, and expect it to generate log.

The Space Comes Back a Little Later

The file doesn’t shrink by itself, and the used space inside it doesn’t drop at once either. SQL Server frees the pages of deleted rows in a background task. The next script waits 15 seconds, then reads the file.

WAITFOR DELAY '00:00:15';
SELECT size / 128 AS FileMB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128 AS UsedMB FROM sys.database_files WHERE type_desc = N'ROWS';

Before the cleanup, 21 MB of the file was in use. Now 4 MB is. The other pages are free. The file is still 72 MB, because freed space inside a file doesn’t go back to Windows by itself.

Shrink Once, Not Every Night

If the file holds many gigabytes of free space, and the disk is short, shrinking is a fair choice once. A shrink moves pages and fragments indexes, so don’t put it on a schedule. The TRUNCATEONLY option releases only the free space at the end of the file and moves nothing.

DBCC SHRINKFILE (BrokerQueueDemo, TRUNCATEONLY) WITH NO_INFOMSGS;
SELECT size / 128 AS FileMB, CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128 AS UsedMB FROM sys.database_files WHERE type_desc = N'ROWS';

How much comes back depends on where the used pages sit, so your file can shrink by less. A full shrink with a target size moves pages and can release more, at the price of fragmentation. TRUNCATEONLY moves nothing, so no index rebuild is needed afterward. A normal shrink moves pages, and then a rebuild of the busy indexes is worth it.

Fix the Cause

Cleaning a queue treats the symptom of MSDB growth. Find out why nobody reads it. Check is_receive_enabled and is_activation_enabled in the first query. Look at sys.transmission_queue for messages that couldn’t be delivered. For Database Mail, check that its queues are being processed and that the mail profile works. A queue that keeps growing after a cleanup still has a reader problem.

You could argue that a scheduled cleanup is easier than finding the cause. It is, until it ends a conversation that something still needs. Fix the reader and the cleanup becomes a one-time job.

What to Remember

To stop MSDB growth, find the big queue with the size query. Look at the endpoints before you end any. Clean only the conversations you own, with WITH CLEANUP. Wait for the space to come back before you measure. Shrink once if you must. When you finish with the demo, drop the test database.

USE master;
GO
ALTER DATABASE BrokerQueueDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BrokerQueueDemo;

A queue is not a trash can, it is a promise that someone will read what you send.

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