Large msdb data file due to excessive Service Broker conversations

Note to readers: This is my first blog post – apologies if it doesn’t read well. The website still needs a lot of work – but I guess I have to start somewhere and feedback is appreciated. I’m going to try find my style and see how it goes. The blog’s main purpose is to help remind me of the quirks I’ve come across and the ways I’ve solved them – trying to keep it short and sweet. I also hope it also helps you, if you ever stumble across it.

Note to self:

Background & Investigation:

Something I came across today at a client site on one of their BizTalk development servers. There was a sizable msdb data file and space was running out quickly on the drive caused by overzealous ssis logging configured by one of the developers. The “Disk Usage by Top Tables Report” gives you the necessary details to see where to look first [but not everything]. Also, checking the error logs, I found the msdb had actually run out of space a few times already:

Error: 1105, Severity: 17, State: 2.
Could not allocate space for object 'dbo.sysssislog'.'PK__sysssisl__3213E83F33EC335A' in database 'msdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Solution:

So, we backed up the msdb and then my colleague truncated the sysssislog but it wasn’t enough. Nor was the usual shrinking of the data file, as that can only go so far and, anyway, it’s not the best thing to do. Also, the “Disk Usage Report” showed 99% utilization and the “Disk Usage by Top Tables Report” showed all tables at small sizes – what gives?

Some further digging around (aka Googling) we found this post that pointed us in the right direction. There were a lot of conversations in [msdb].[dbo].[WMIEventProviderNotificationQueue].

453,538 to be exact, under multiple conversation handles – all related to the deadlock event notifications that were never cleaned up during testing.

At this stage, since getting the space freed up was the priority, one way to clear the space is to run the following. This would get you a list of conversation handles and the number of associated conversations:

SELECT conversation_handle, count(conversation_handle)
FROM [msdb].[dbo].[WMIEventProviderNotificationQueue] WITH(NOLOCK)
GROUP by conversation_handle

All you need to do then is make sure you have sufficient space on your log drive and execute the following T-SQL (for example, if the conversation handle was : ‘F71BE6C4-C864-E611-80D3-005056A547EE’):

DECLARE @handle uniqueidentifier;
SET @handle = 'F71BE6C4-C864-E611-80D3-005056A547EE';

END CONVERSATION @handle WITH CLEANUP;

OK, now it’s done, do the shrink(s), rebuild indexes and call it a night :).

Edit: 24/08/2016 – formatted code!