4

I dropped a char(10) column from a table that has about 111 million rows (30gb data, 11gb index)

I then ran an index rebuild on the clustered index. This ate up all the space on the drive containing the database.

I then ran dbcc cleantable and got all the space back, plus a few more gigs.

Is there a way to reclaim space from the dropped column when there isn't enough space for index rebuild?

Or did the cleantable operation actually reclaim the space? (I know it reclaimed space, but I'm guessing it's possible it reclaimed space for a different reason, since cleantable isn't suggested as the way to reclaim space after dropping a column)

3

DBCC CLEANTABLE can only be used to reclaim space left from dropping a variable length column. Thus it cannot be considered a "good" replacement for rebuilding the table/index.

Since you state that the column you dropped was a fixed length column, I can only surmise there must be some other reason for unused, but allocated space being present in the table; perhaps ghost records or perhaps you actually dropped some other columns as well that actually were variable length.

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.