Chad Crawford (7/28/2009)
Hugo (et al.), I was running some scripts (in 2K5) based on this question to learn a little more and I noticed that the delete doesn't seem to return any space at all, even before reloading all the data back in?!? I filled your table with 1,000,000 records, copied the rows to a new table, then did a delete with no where (scary all by itself) and sp_spaceused showed the same amount of reserved space as before I ran the delete. Is it really holding on to all those pages still? I looked in the BOL under Dropping and Rebuilding Large Objects and it seems to indicate that the cleanup doesn't happen until after the transaction commits, but the result is the same whether or not I wrap it in a transaction. It's the oddest thing - sp_spaceused shows 0 rows taking up 47MB. :ermm: I understand now why the same space is used after reloading the data, but what about when there is no data at all? I never expected that.Thanks,
Chad
Hi Chad,
This is strange. I ran some tests as well, and I found that deleting all rows does free up most space, but not all. I also found that most space is released immediately, but there's also a bit released only after a second or so. And the most surprising is that the amount of unreleased space seems to depend on various factors I could not put my finger on - at one time, I consistently was left with about 200 KB or so after deleting (with a 10,000 row test); later when I tried again (but apparently not with the exact same table and data), I was consistently left with a mere 32KB.
However, this is a bit off-topic here, as the QotD was not about reclaiming free space after deleting all or any rows from a table, but after deleting one column without removing any row.