So each week, I am required to turn in a weekly status report that demonstrates in summary fashion what I have accomplished the previous week. In a smoothly running SQL infrastructure of even 90+ servers, DBA managers may find themselves in a repetitive rut of similarity week to week.  And for the DBA who reports to a network engineer, it really boils down to "SQL Good" or "SQL bad". When "SQL bad", which should happen less than 3% of the time heroic action is required. 

It just so happened recently, reported fully in my WSR, that I noticed that an MSDB database had grown to over 8G and was growing by at least 100M per day.  There was about 1G left on the C: drive where SQL was installed (not recommended) so I did not panic but I had to find out. I could have asked one of my proficient DBAs to investigate, but with the side-sagging over glut of non-grunt work, I was feeling like I would take this...I got it, seriously.  First...find out where the 8G was coming from. Not as easy as it should have been.  sp_msforeachtable to get row sizes did not work on the system database. I could have investigated. However, why not a quick cursor to get row counts. Still nothing revealed but my own skin-scraped embarassment at spending more than 20 minutes on this.  I eventually came by and by to the sys.sysxmitqueue table.  Being that it is a sys. table, it was not easily accessible, nay, it was unobservable by standard means.  With a quick bit of research, I found the view that exposes this sys table and that is: sys.transmission_queue. There were close to a million records there.  This all happened because someone long ago wanted to understand Service Broker..a noble idea, but without the understanding of how to turn it off, even after disabling Service Broker itself, or the endpoint that was created.

The database, msdb, was still enabled for the Broker and was apparently trying to send many many messages that it could not send.

 SQL Bad.

Surely it would only be a matter of minutes to clean all of this up. Well, kind of.  Because the data file was so full, shrinking was not going to be useful.  I had to remove these failing messages. The way I discovered to do that was to create a new broker, which would flush out all messages, supposedly.

Alter database msdb Set New_BROKER

This did nothing that I saw but get blocked behind other processes (BRKR_Task as I recall but this was the beginning of the week and I do not keep good notes for my WSR)

So, next I disabled it.

Alter database msdb Set Disable_BROKER

This worked but did not flush anything but my face.

So I tried to end the conversation, which should have definitely flushed everthing.

END CONVERSATION '{1F9B06DB-7B4B-DC11-B999-005056A249D6}' WITH CLEANUP ;

And it began surely to remove data. But..unfortunately, it must have used delete statements because in about 10 minutes the log file filled to almost a gig. Remembering that I only had a Gig left to begin with, this was obviously not good. With 7.94Megs left on the drive, I now panicked.

Kill process...roll back...shrink log...turn in report.

At least, I thought, it will not try to send any more messages. The next day I discovered that I had 3G available...a small unexpected gift.  So, it did work. 

When I was in the trenches and doing this everyday, I would have wanted to know exactly what happened and I still do..the difference now is that I do not have to stay up until 3:00AM duplicating the issue just so I can sleep. 

I AM losing the edge.