— 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:
— 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]
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
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….
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!