After cleaning my table with DELETE FROM MyTable (and executing DBCC shrinkdatabase('MyDB') should that matter) I run the statement EXEC sp_spaceused MyTable. The results confuse me:

tableName   numberOfRows   reservedSize   dataSize   indexSize   unusedSize
---------   ------------   ------------   --------   ---------   ----------
MyTable     0              21664 KB       20672 KB   736 KB      256 KB

As you can see there are zero rows, yet there's almost 21 MB of data. My question is: what are possible causes for this situation and/or how can I further investigate this?

up vote 5 down vote accepted

DELETE does not reclaim space, it deletes rows.

Space can remain allocated for several reasons, 3 of which are:

  • ghost clean up is still running
  • the table has no clustered index
  • the system tables are not updated yet


  • EXEC sp_spaceused 'MyTable', 'true' to force a space used update
  • TRUNCATE TABLE which deallocates space, rather then deleting rows
  • DELETE myTABLE WITH (TABLOCKX) if no clustered index and can't use TRUNCATE

Don't run DBCC shrinkdatabase because it adds no value and it will simply grow again

  • Your first suggestion was half the solution, size is now down from 21MB to 13MB. As to the other suggestions: the table has a clustered index on the PK, and Truncate unfortunately couldn't help as the table is referenced by a FK. I'll have a look at the ghost clean up as well. – Jeroen Dec 14 '11 at 10:47
  • 1
    The other 13 MB is now gone, I'm guessing the ghost clean up bit was my missing piece. – Jeroen Dec 14 '11 at 14:17

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.