data file of MSDB database has been increasing(sys.sysxmitqueue)

One day my service now dash board thrown an error saying as data file msdb consuming 67.5 GB out of 68 GB and only 500 mb is left out. My client sent a mail to me to find out a root cause of analysis.And he also mentioned and provided a query and said that there is a system table called  sys.sysxmitqueue is consuming nearly 67GB. When i try to find out this table under msdb database system tables we don't find this.  But realized this is a hidden table and the same has been found when we run the below query.

USE msdb
GO
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(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) LIKE '%sysxmitqueue%'
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

See the below query results, you will find "total space MB" in zeros's if you have problem in your environment you find the size of data file that is occupying in the drive that space would be shown here.

Run the below command in msdb 

SELECT conversation_handle,to_service_name 

from sys.transmission_queue

If you run the above command you can see the data in the view like below.

99591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
84591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
87591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
8A591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
96591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
8D591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
93591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
90591843-A208-E611-A4B5-0022190A2635 DeadlockNotificationService 
75591843-A208-E611-A4B5-0022190A2635 BlockedProcessNotificationService 

These values are there in the table because Client is using Service broker . And those notification services are under service broker are disable mode like below. Check these under a maintenance database. In my environment my maintenance database name is MaintDB. Under this database,under service broker and under Queues(not under system queues) client created two notification services. Those two names are "DeadlockNotificationService" another "Blocked ProcessNotificationService"

By the time Issue occurs those two notification queues are in disable mode. So these information pile up in the sys.transmission_queue table. To delete this Conversation handle from sys.transmissioin_queue  table. We can not use DELETE or TRUNCATE commands on this. 

Run this below command to delete to 

END CONVERSATION 99591843-A208-E611-A4B5-0022190A2635 WITH CLEANUP ; 

above query will delete all the conversation handles from  sys.transmission_queue.

Next take the full backup of msdb, after taking the full backup of msdb then shrink the DATA file of MSDB and it will automatically free up the space and you can see the free space in the respective drive.

So sometimes we need to check whether these services are enabled or not if these are not enabled msdb disk space might occurs due to notification services data will pile up in sys.transmission_queue. But the table it shows from msdb end is sys.sysxmitqueue

END CONVERSATION 'C6EFF799-21F7-E811-9109-005056A05BD2' WITH CLEANUP ; 

--If you want to directly execute the query un comment exec(@stmt)

USE msdb
go
SET NOCOUNT ON
DECLARE @I INT,@Count INT,@Name varchar(100),@Stmt NVARCHAR(2000)
--DECLARE @Count INT
--DECLARE @Name varchar(100)
--DECLARE @Stmt NVARCHAR(2000)
SET @I=0
SELECT @Count=COUNT(DISTINCT conversation_handle)
from sys.transmission_queue WITH (NOLOCK)
DECLARE  @tABLE TABLE
(Sno INT IDENTITY(1,1),[Name] VARCHAR(100))
INSERT INTO @tABLE 
select DISTINCT conversation_handle
from sys.transmission_queue WITH (NOLOCK)
WHILE @i<@Count
BEGIN
SET @i=@i+1
SELECT @Name = [Name]
from @tABLE 
where Sno=@i
--print @Name
SET @Stmt= 'END CONVERSATION '''+@Name+''' WITH CLEANUP;'
PRINT @Stmt
--EXEC(@Stmt)
END