All DBA and Developers must have observed when any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild. There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.
DBCC command for reclaiming space is very simple. Following example is for AdventureWorks database and Person.Contact table.
DBCC CLEANTABLE ('AdventureWorks','Person.Contact', 0)
The result of DBCC is displayed below.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC is fully logged operation. It also does not affect temp tables and system tables.
Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL – DBCC CLEANTABLE
can you explain why value 0 at the end of the statement
( ‘AdevntureWorks’ , ‘Person.Contact’, 0)
DB Table ?
AdventureWorks = DB
Person.Contact = Schema.Table
0 = ????????????????????
The 3rd parameter is for batch size. It determines the no. of steps to reclaim space. If given as 0, it reclaims steps in a single transaction
HTH,
Suprotim Agarwal
—–
Thanks Pinal, and thanks Suprotim for clarifying what the ’0′ is.
[...] in the SQL Server Shrinking Database is Bad – Increases Fragmentation – Reduces Performance Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE Data and Page Compressions – Data Storage and IO [...]