what is using up my database space?

• SQL_EXPAT

  SSCrazy

  Points: 2708

  More actions

  October 23, 2009 at 6:26 am

  #227412

  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

• abair34

  Ten Centuries

  Points: 1076

  More actions

  October 23, 2009 at 6:48 am

  #1069972

  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

• SQL_EXPAT

  SSCrazy

  Points: 2708

  More actions

  October 23, 2009 at 7:06 am

  #1069986

  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