SYSK 218: Reclaim Space Immediately After Dropping a Column

  • Article

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!