4

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?

5

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

Try

  • 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

2
  • 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 agree to our terms of service, privacy policy and cookie policy

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