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;


// 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;



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!


Comments (0)

Skip to main content