MSDB database files grow to a large size, but apparently without any large tables in the database

We faced a SQL Server 2005 / SP4 issue where the .MDF of the MSDB database had grown to over 60GB, but a cursory examination of the MSDB tables revealed only a few megs worth of data.  The initial guess was runaway job histories, but only a few thousand rows were in each table.  I normally use Bill Graziano's big tables script or the canned SQL reports (Disk usage by top tables), but according to both there was nothing taking up any space.  So what was consuming 60GB of data ?

The canned reports (and most scripts from the internet) don't include the system tables as part of the query.   An analysis script from MSSQLTips included everything, and quickly revealed the culprit - sysxmitqueue, clocking in at just over 60GB.

 

sysxmitqueue

This is a service-broker related table; it stores information about queued messages and their respective errors.  It can't be modified directly, but there are a few ways to clear it out.  If you have space available to remove each row via a logged operation, you can use END CONVERSATION with the Conversation Dialog ID from sys.transmission_queue or sysxmitqueue.   But you may need something quicker and unlogged like we did - ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE will reset the service broker completely and truncate any existing messages.  Keep this in mind if you're actually using service broker on that system.  Credit to the Microsoft forums for this solution.  A post on SQLServerCentral suggests you might be able to just drop the queue instead of resetting the broker, but I didn't verify this directly.

After using the set NEW_BROKER command, we were able to shrink the .MDF.