Large MSDB From Hidden sysxmitqueue Table

By Lori Brown | Expert

Oct 13

— by Lori Brown @SQLSupahStah

Recently, I was checking on some of the servers that I manage and found that one had an 8GB msdb database. While that is not too bad it is kind of large so I figured that there must be something that needed to be cleaned up. I ran the Disk Usage by Top Tables report expecting to see a table that was huge to leap out at me. However, this time there was nothing. As a matter of fact, it looked like the largest table only had about 187MB of data in it. So, what in the world was going on?

I did some looking around and found that there is a hidden and undocumented table named sysxmitqueue that can sometimes become huge if you have event notifications set up. If there are errors in how the notification is set up, there will be lots of error messages in the hidden table and the transmission_queue view.

Run this query to find out of you have msdb bloat from the sysxmitqueue table:

USE msdb

GO

— check for size of sysxmitqueue table

SELECT object_name(i.object_id) AS TableName,

i.[name] AS IndexName,

(sum(a.total_pages)*8)/1024 AS TotalSpaceMB,

(sum(a.used_pages)*8)/1024 AS UsedSpaceMB,

(sum(a.data_pages)*8)/1024 AS DataSpaceMB

FROM sys.indexes i

INNER JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a

ON p.partition_id = a.container_id

WHERE object_name(i.object_id) = ‘sysxmitqueue’

GROUP BY i.object_id, i.[name]

GO

In my case, I found that this table had almost 8GB of data in it. Bingo!! Gotta clean it up now. Once again after doing some research, I found that lots of people have had problems with this table. Some say to set a new broker like so:

— DON’T USE THIS IN PRODUCTION!!

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE

But that will cause problems if you do it in a production system because the service broker id will be different than is being used by anything else that has distributed conversations. If you do this, you could leave conversations stranded.

There is also a way to end conversations that takes a little time but will not cause issues if you are careful. I found that I could not query the sysxmitqueue at all but found that info from this table is also found in the sys.transmission_queue (https://msdn.microsoft.com/en-us/library/ms190336.aspx) view. You can query it easily.

— get list of conversations

SELECT DISTINCT conversation_handle, to_service_name

FROM sys.transmission_queue

I used the above query to get a list of the conversations that needed to be cleaned up. The to_service_name column is the service name you created while setting up event notifications. You can use values from the conversation_handle column to tell an “END CONVERSATION…” statement (https://msdn.microsoft.com/en-us/library/ms177521.aspx) which conversation exactly to end.

— end open conversations

END CONVERSATION ‘4E5EF6AE-90F8-E311-B9CF-842B2B514244’ WITH CLEANUP

If you have a lot of open conversations, running this manually can take a while but you won’t nuke anyone else’s conversations this way.

The last thing I need to do is figure out why my event notification is not ending conversations correctly. That will be another post later so….

blog_20161013_1

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>