Problem solve Get help with specific problems with your technologies, process and projects.

Use DBCC CLEANTABLE to reclaim unused space in SQL Server tables

This tip shows you how to use DBCC CLEANTABLE to reclaim unused space in your SQL Server tables.

This tip was submitted to the tip library by member Parthasarathy Mandayam. Please let others know how useful it is via the rating scale at the end of the tip. Do you know a useful tip, timesaver or workaround? Submit it to our monthly tip contest and you could win a prize.

This is a great tip for Database Administrators.

If you ever end up removing a variable-length column (varchar) or a 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 parameter is the table, 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 challenge, requiring the batch approach.

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

Do you have comments on this tip? Let us know.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning