October 23, 2009 at 6:26 am
I have a database that has 40GB of used space (this is the size of my backup). All tables are practically empty. All service broker queues are empty. I don’t understand what is using the space up. I’ve re-indexed everything. Weird. Anyone know what I’m missing here. My guess is that its some Service Broker internals… How do I clear this space down?
database_name database_size unallocated space
ParisTD 250000.00 MB 159283.55 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
41693648 KB 41367960 KB 288176 KB 37512 KB
thanks
SQL_EXPAT
October 23, 2009 at 6:48 am
Not sure if this will help but try running this query... It will show you pages allocated for some system objects as well. Also sum up the MBs_Used column there and see if it equals the figure that you're reporting back as used space due to your backup size.
SELECT OBJECT_NAME([object_id])
, (SUM(reserved_page_count) * 8) /1024 as MBs_Used
FROM sys.dm_db_partition_stats
GROUP BY [object_id] ORDER BY 2 DESC
Good Luck
-Mike
October 23, 2009 at 7:06 am
Nice one - thanks. My suspicions were correct - Service Broker:
(No column name)MBs_Used
sysdesend19140
sysdercv14279
sysconvgroup7216
queue_messages_8838738418
Looks like something to do with SENDs and RECEIVEs.
thanks
SQL_EXPAT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply