DBCC CLEANTABLE can reclaim selected space left after dropping variable-length columns. I keep that table operation separate from shrinking a database file.
Dropping a variable-length column doesn’t immediately reclaim all its allocated space. CLEANTABLE supports selected column changes when that reclamation is needed. I check the table and storage requirement before using it.
-- Administrator template for a deliberately selected table after column changes.
DBCC CLEANTABLE (N'YourDatabase', N'dbo.YourTable', 1000);The third argument controls batching. Review logging, locking and the workload before scheduling it. The command doesn’t support temporary or system tables, and it doesn’t reclaim space from dropping a fixed-length column.
I’ve seen little use of CLEANTABLE in practice. Most teams reach for a database shrink instead. Those operations solve different problems, so I review the actual storage need first.
Measure Before and After
Set the actual database and table before performing maintenance. A completion message proves the command ended, not that a particular number of pages was freed. Measure the table’s space before and after:
EXEC sys.sp_spaceused N'dbo.YourTable';Compare the data and unused values from the two runs. Don’t promise space reclamation merely because a declared maximum length was reduced.
The database can reuse unused space inside its files. Shrinking the file is a different operation with separate costs. I don’t combine those decisions automatically.
Reclaimed table space is not automatically a smaller file, it is space the database can reuse.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.