SQL SERVER – Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE

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

8 thoughts on “SQL SERVER – Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE

  1. Pingback: SQL SERVER – Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31 « SQL Server Journey with SQL Authority

  2. I performed LTRIM & RTRIM, and reduce the column size (all varchar) to be the column specific max length. The origina table had much larger column size (varchar as well).

    Problem: data space has doubled when adjusting the column size compared to the original table (which had far larger column size).

    I use DBCC CleanTable, and recover only 2MB. No indexes or keys on both tables. Original table @ 87MB, newly reduce table @ 178.76MB.

    What am I doing wrong?

    Original table:

    CREATE TABLE [dbo].[Original](
    [RequestId] [int] NOT NULL,
    [QuoteNumber] [varchar](25) NULL,
    [ResponseTime] [datetime] NOT NULL,
    [ResidenceShape] [varchar](80) NULL,
    [ExteriorFeatures_ExteriorDoors] [varchar](300) NULL,
    [ExteriorFeatures_SpecialtyWindows] [varchar](300) NULL,
    [DetachedStructure1] [varchar](70) NULL,
    [AttachedStructure1] [varchar](150) NULL,
    [AttachedStructure2] [varchar](150) NULL,
    [AttachedStructure3] [varchar](150) NULL,
    [GarageType1] [varchar](100) NULL,
    [GarageType2] [varchar](100) NULL,
    [BathroomFull] [varchar](75) NULL,
    [BathroomHalf] [varchar](10) NULL,
    [BathroomOneAndHalf] [varchar](10) NULL,
    [InteriorFeatures_FirePlace] [varchar](200) NULL,
    [InteriorFeatures_Lighting] [varchar](200) NULL,
    [InteriorFeatures_Staircase] [int] NULL,
    [InteriorFeatures_InteriorDoorsMillwork] [varchar](200) NULL,
    [InteriorSystems_HeatingSystem] [varchar](200) NULL,
    [InteriorSystems_CoolingSystem] [varchar](200) NULL,
    [KitchenSize] [varchar](70) NULL,
    [KitchenCounter] [varchar](70) NULL,
    [KitchenCabinets_GlassCabinetDoors] [varchar](50) NULL,
    [KitchenCabinets_PeninsulaBar] [varchar](50) NULL

    New table:

    CREATE TABLE [dbo].[New Table](
    [RequestId] [int] NOT NULL,
    [QuoteNumber] [varchar](14) NULL,
    [ResponseTime] [datetime] NOT NULL,
    [ResidenceShape] [varchar](25) NULL,
    [ExteriorFeatures_ExteriorDoors] [varchar](75) NULL,
    [ExteriorFeatures_SpecialtyWindows] [varchar](62) NULL,
    [DetachedStructure1] [varchar](38) NULL,
    [AttachedStructure1] [varchar](23) NULL,
    [AttachedStructure2] [varchar](23) NULL,
    [AttachedStructure3] [varchar](23) NULL,
    [GarageType1] [varchar](50) NULL,
    [GarageType2] [varchar](50) NULL,
    [BathroomFull] [varchar](2) NULL,
    [BathroomHalf] [varchar](1) NULL,
    [BathroomOneAndHalf] [varchar](1) NULL,
    [InteriorFeatures_FirePlace] [varchar](35) NULL,
    [InteriorFeatures_Lighting] [varchar](97) NULL,
    [InteriorFeatures_Staircase] [int] NULL,
    [InteriorFeatures_InteriorDoorsMillwork] [varchar](99) NULL,
    [InteriorSystems_HeatingSystem] [varchar](31) NULL,
    [InteriorSystems_CoolingSystem] [varchar](28) NULL,
    [KitchenSize] [varchar](23) NULL,
    [KitchenCounter] [varchar](29) NULL,
    [KitchenCabinets_GlassCabinetDoors] [varchar](2) NULL,
    [KitchenCabinets_PeninsulaBar] [varchar](3) NULL

    Like

  3. when i am execting DBCC CLEANTABLE(‘catar’,’temp_val_dia_fin’,0)..
    system show below error message …
    “Msg 211, Level 23, State 51, Line 1
    Possible schema corruption. Run DBCC CHECKCATALOG.”

    I am not able to drop the above table

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s