Reclaim Space After Dropping a Column With DBCC CLEANTABLE

To reclaim space after dropping a column, run DBCC CLEANTABLE or rebuild the index. The two do not give the same result.

A Dropped Column Keeps Its Bytes

When you drop a variable-length column, SQL Server removes it from the table definition. The old bytes stay inside every row. The drop is quick for that reason, because it only changes metadata. The rows stay as wide as before, and the table stays as large as before.

The demo shows it. The database is named CleanTableDemo. The Recipes table has a short title, a medium note column and a long steps column. The script loads 5,000 rows with GENERATE_SERIES, which needs SQL Server 2022 and compatibility level 160. It also creates a small view that reports page count, row size and page fullness.

IF DB_ID(N'CleanTableDemo') IS NULL CREATE DATABASE CleanTableDemo;
GO
USE CleanTableDemo;
GO
DROP TABLE IF EXISTS dbo.Recipes;
CREATE TABLE dbo.Recipes (RecipeID int IDENTITY(1,1) PRIMARY KEY, Title nvarchar(60) NOT NULL, Notes varchar(max) NOT NULL, Steps nvarchar(2000) NOT NULL);
INSERT INTO dbo.Recipes (Title, Notes, Steps)
SELECT CONCAT(N'Recipe ', value), REPLICATE(CAST('n' AS varchar(max)), 300), REPLICATE(N's', 1500)
FROM GENERATE_SERIES(1, 5000);
GO
CREATE OR ALTER VIEW dbo.RecipeSpace AS
SELECT ips.page_count AS Pages,
       CAST(ips.avg_record_size_in_bytes AS decimal(10,1)) AS AvgRecordBytes,
       CAST(ips.avg_page_space_used_in_percent AS decimal(5,1)) AS PageFullPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Recipes'), 1, NULL, 'DETAILED') AS ips
WHERE ips.index_level = 0 AND ips.alloc_unit_type_desc = N'IN_ROW_DATA';

Read the view, drop the long column, and read the view again.

SELECT N'before' AS Step, * FROM dbo.RecipeSpace;
ALTER TABLE dbo.Recipes DROP COLUMN Steps;
SELECT N'dropped' AS Step, * FROM dbo.RecipeSpace;

Nothing changed. The column is gone from the definition, and each row still holds its 3,000 bytes of old steps. The pages are 82.6 percent full of data nobody can read.

What DBCC CLEANTABLE Does

The command DBCC CLEANTABLE reclaims space from dropped variable-length columns. It takes the database, the table and a batch size. The batch size is the number of rows processed in one transaction. A value of 0 runs the whole table in one transaction. That can make the log grow on a large table. Pick a batch size above 0 for a big table, for example 10,000 rows. The right size depends on your log.

DBCC CLEANTABLE (N'CleanTableDemo', N'dbo.Recipes', 0) WITH NO_INFOMSGS;
SELECT N'cleaned' AS Step, * FROM dbo.RecipeSpace;

The rows shrank from 3,340.6 bytes to 338.6 bytes. That is the dropped column leaving. The page count did not move. The same 2,500 pages are now 8.4 percent full. DBCC CLEANTABLE gives the space back inside each page, where new rows can use it. It does not return pages to the database.

What a Rebuild Does

To reclaim space after dropping a column fully, rewrite the table. An index rebuild writes the table again, so it removes the dead bytes and packs the pages. For a table with a clustered index, rebuild the clustered index. For a heap, use ALTER TABLE with REBUILD. The next statement rebuilds every index on the demo table.

ALTER INDEX ALL ON dbo.Recipes REBUILD;
SELECT N'rebuilt' AS Step, * FROM dbo.RecipeSpace;

The table went from 2,500 pages to 218. Rows stayed at 338.6 bytes and the pages are 96.5 percent full. A rebuild does everything CLEANTABLE does and returns the pages as well. That is why I prefer the rebuild over the DBCC command when both are possible. The rebuild also refreshes the statistics of the index.

How to Know a Table Needs It

Run the view on a table you suspect. Three signs point to wasted space. The page count is high for the number of rows. The average record is small. The page fullness is low. The DETAILED mode reads every page, so use it on a small table, and use SAMPLED on a large one. Change DETAILED to SAMPLED in the view for a large table.

When DBCC CLEANTABLE Is Still the Right Tool

A rebuild costs more. It needs free space for the new copy and writes every page. It can block users unless the edition supports an online rebuild. With a batch size above 0, DBCC CLEANTABLE works in batches of rows. A long run then does not hold one huge transaction. Use it when the table is large and the window is short. A half-empty page is acceptable until new rows fill it.

The command is meant for variable-length columns. For a dropped fixed-length column, rebuild instead. Check the data type of the column you dropped before you pick the tool.

You could argue that you should plan the column drop and skip the cleanup. If you plan a rebuild after every large drop anyway, you are right, and you can ignore DBCC CLEANTABLE. The command matters when you did not plan the rebuild and the table is already in use.

What to Remember

To reclaim space after dropping a column, measure first with the view above. The dropped bytes stay in the rows until something rewrites them. DBCC CLEANTABLE shrinks the rows and keeps the pages. A rebuild shrinks the rows and returns the pages. Use a batch size above 0 on a large table, and watch the transaction log while it runs.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE CleanTableDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE CleanTableDemo;

A dropped column is not gone, it is hidden until something rewrites the row.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.