SYSK 218: Reclaim Space Immediately After Dropping a Column
If you are deleting a column of varchar(max), text, ntext, or image data type, your database has likely allocated a large amount of space for the stored data.
When you drop a column, it’s always a good idea to rebuild indexes which may help you reclaim the unused space. But if rebuilding indexes is an intensive operation then you have a second option to reclaim the unused space from a table – the DBCC CLEANTABLE.
DBCC CLEANTABLE command is used on a table to reclaim space after a variable-length column is dropped:
Use Master;
Go
// Third parameter is the batch size
// If 0, it will process the entire table as one transaction;
// number other than zero indicates the number of rows per transaction
DBCC CLEANTABLE ('YourDBName','YourTableName',0) with NO_INFOMSGS;
Go
IMPORTANT: This command exclusively locks the table during the operation
NOTE: DBCC CLEANTABLE is not supported for system tables and temp tables.
DBCC CLEANTABLE should not be used as a routine maintenance task. You must use this only if you have made significant changes to variable-length columns in a table or indexed view and if you need to reclaim the unused space immediately.
Special thanks to Saleem Hakani for this information!
