I had an interesting problem today where a developers database had grown nearly double its size and after deleting nearly all the data in the tables still had the same issues

When I looked at standard SSMS -> Report -> Disk Usage however I still got

bloating01

Interesting not much free space to grab back here, when there should clearly be 100s of gb free.  They looked at the table report as well which gave similar strange results as all the tables were empty.

This is were we come in, i decided to look at the tables sizes via a T-SQL query instead of the reports,  I used a Pinal Dave example from here:

SQL Server Size of Index Table for each Index

Which got me:

bloating02

Now that looks a lot more interesting! Sysxmitqueue is rather large! now you cant directly query this table unless you login via the DAC. but you can query a view on the table sys_transmission_queue and get some information to help resolve it (More on that below). For us we weren’t using Service Broker or Event notification so the easiest way to wipe this is using:

ALTER DATABASE [MyDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

For people that do have SB running and need to be a bit more clever, you can try reading Robert L Davis post below:
SERVICE BROKER GONE WILD: DEALING WITH SYSXMITQUEUE BLOAT

Advertisements