SQL SERVER – Huge Space Used by Table sysxmitqueue in MSDB. How to Clear it Quickly?

SQL SERVER - Huge Space Used by Table sysxmitqueue in MSDB. How to Clear it Quickly? msdb-800x697 Recently I was helping a customer who hired me for my services. He had an issue where they noticed that the size of the MSDB database has grown huge. Most of the situations I have been contacted when LDF (transaction log file) for the database is huge. But this time it was data file which was consuming a lot of space.

In the past, I have seen huge MSDB size and written below blogs.

If you are not hitting one of the above-listed issues, then read further.

Since we confirmed this was due to the MDF file, we needed to figure out which table is consuming a lot of space.  Below is the query which I used:

SELECT OBJECT_NAME(object_id) AS Table_Name
	,SUM(rows) AS Total_Rows
FROM sys.partitions
WHERE index_id IN (0,1)
GROUP BY object_id

We found that it was sys.sysxmitqueue table which was bloated in size. Microsoft documentation says this table contains a row for each Service Broker transmission queue. I asked from my client if they are aware of any service broker implementation on this server and someone told that they did deploy some monitoring script.


This should be implemented if you are sure that you are OK to lose the data in the service broker queue. I double checked with my client and they were OK to clear all conversations from the queue because they were not needed. In fact, they were ready to clean up whatever objects were created by the script. Here is the silver bullet to fix the issue.


As I mentioned above, the script would discard ALL the messages without attempting delivery.

If you are using service broker for your application, then you need to fix the application that is sending those messages else database will grow back again.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server, System Database, Transaction Log
Previous Post
SQL SERVER – DBCC CLONEDATABASE Error – Msg 2601: Cannot Insert Duplicate Key Row in Object ‘sys.sysowners’ With Unique Index ‘nc1’.
Next Post
SQL SERVER – Fix: Logical Name Mismatch Between Catalog Views sys.master_files and sys.database_files

Related Posts

1 Comment. Leave new

  • Hi, I recently saw (or thought I saw) the sysxmitqueue table growing even after I had dropped the server_event_notification that caused the initial problem (select * FROM sys.server_event_notifications was returning no records). Is this even possible? I am not aware of anything else in the system that would have been generating such records.


Leave a Reply