Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 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

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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