SQL Data Drive Almost Full? Why is MSDB GIGANTIC!!!!????

I came in a few weeks ago to this potential catastrophe. One of my SQL servers had only a few gigs of space left on its data drive, so I opened a timeline graph to see if we were just running out of room because of normal data growth, or whether there was an issue. There was an issue. The size of my MSDB file was 15.5 GB! I hunted around on the net for answers to how this could possibly happen, and in the end I fixed the issue without the server running out of space. What was happening was that there was a reindexing maintenance plan that stalled out and was just stuck writing to MSDB.

I also feel the need to say that I'm a noob accidental DBA. What that means is that I know the most about SQL in my workplace, so I'm automatically in charge of SQL. I've learned a lot, but I totally Google-Fu'd my way through this problem.

Here's what I ended up doing to fix the issue:

1. I found that the culprit was backed up messages in the sysxmitqueue by running this script:

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

GROUP
BY i.object_id, i.index_id, i.[name]

ORDER
BY sum(a.total_pages)
DESC,object_name(i.object_id)

GO

2. I stopped the maintenance job that was stuck.

3. I then ran
SELECT * from sys.transmission_queue

This gave me the conversation handle that was stuck (9A06F198-5008-E011-9526-0050569F69D3).

4. I then ran
End Conversation 9A06F198-5008-E011-9526-0050569F69D3 with cleanup

This stopped the conversation and purged the table in MSDB. It took around 20 minutes to run.

5. I kicked everyone off the system. A quick way to see connections to your SQL server is to run sp_who2. This lists all of the connections.

6. I stopped the SQL Server Agent Service

7. I ran the following SQL command:
Alter Database msdb 
Set New_Broker With Rollback Immediate

8. I right-clicked on the MSDB database, selected tasks, then shrink -> database

9. This reclaimed 15 of the 15.5 GB. Crisis averted!