This tip was submitted to the SearchSQLServer.com 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.
