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.

Solarwinds

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
ORDER BY 2 DESC;

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.

WORKAROUND/SOLUTION – MSDB

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.

ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

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)

Solarwinds
, , , ,
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

Leave a Reply

Menu