All DBA and Developers must have observed when any variable length column is dropped from the table, it does not reduce the size of the 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 the variable length column is also modified for lesser length.
DBCC command for reclaiming space is very simple. Following example is for AdventureWorks database and Person.Contact table.
DBCC CLEANTABLE ('AdventureWorks','Person.Address', 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.
Additionally, I would like to know if you use this particular feature on your production server or not. Personally, I have not seen much usage of this feature in the action. I believe users may be shrinking their database and not using this feature. Remember Shrinking Database is not a good thing.
Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
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.
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
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
Hi Pinal,
Can you please put some light on how the page allocation works while creating a table, and how it behaves on deletion of coloumns.
How should the same concept work in the case of varchar as the actual byte allocation is done dynamically.