devxlogo

Use DBCC CLEANTABLE to Reclaim Unused Space in Your SQL Server Tables

Use DBCC CLEANTABLE to Reclaim Unused Space in Your SQL Server Tables

This is a great tip for DBAs. If you ever end up removing a variable-length column (varchar) or text column from one of your tables, a certain amount of wasted space will linger in the table’s physical representation. You can reclaim this space with the DBCC CLEANTABLE statement.

To use DBCC CLEANTABLE, pass two required parameters and an optional batch size as a third. The first parameter is the database name (character data), or database id (integer data). The second is the table, or view name or id (again, character and integer data, respectively). If you specify a batch size, the reclamation job is carried out in multiple steps, each equal to the batch size you provided; otherwise, the entire job is attempted in a single step. This may occasionally present timeout or log space challenges, requiring the batch approach.

**Note: This technique doesn’t work when you remove a fixed-length column.

devx-admin

Share the Post: