Space used does not get changed after dropping a column

Sometime back I came across a question in one of the forums. The problem description is as follows.

  • The user had a huge table
  • Due to some reasons he decided to drop a couple of columns
  • Even after the dropping the columns, the space used by the table remained the same as earlier.

Whenever I come across a situation like this, the first question that comes to my mind is “Are those variable length columns?“. Variable length columns are the ones which have the data type as varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Whenever a column having these data types is dropped, the space utilized by this column is not automatically reclaimed. Here is an example. I create a table which has a column with varchar data type.

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NULL,
	[Name] [varchar](100) NULL,
	[Address] [varchar](1000) NULL
) ON [PRIMARY]

After inserting few rows into the table, I drop the column Address. Before and after dropping the table, I check the Average Page Space used in Percentage value.

SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed')
go
ALTER TABLE [TestTable] DROP COLUMN [Address]
go
SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed')

After executing the query, the output looks like the one below.

As evident from the output, the page space used is the same. The space used by the Address column is not released.

In this situation DBCC CLEANTABLE command comes to the rescue. Executing this command after dropping a variable length column will release the space used by that column. Hence I include the following line after the DROP TABLE command.

DBCC CLEANTABLE ('dbname', 'TestTable', 0)

After executing the same query with this extra line, the output looks like this.

DBCC CLEANTABLE had done the trick! This command comes in handy especially in Non-Production instances, where drive space is a constraint and adding/dropping columns is a usual practice.

11 thoughts on “Space used does not get changed after dropping a column

  1. sudhir koduri

    Hi Pradeep,

    I have been following your blog for a while and really do appreciate your effort. The tips you share are greatly useful.I just want to share with you an issue I came across some time back where after deleting over millons of records from the table; I could not reclaim the table space, sp_spaceused for that table was still the same old, i did dbcc updateusage but no use. I was told to “rebuild the table data” and that fixed the issue.the table does not have a clustered Index on it.Hence rebuilding indexes did not help. Do you think “dbcc cleantable” could have fixed the issue, in this case?

    thanks,
    Sudhir Koduri

    Reply
    1. PradeepAdiga Post author

      Thanks Sudhir. Glad you like my stuff. From your description, if the columns were of variable length data type running DBCC CLEANTABLE should have helped. Try this out the next time you delete data from that table.

      Reply
  2. Widgets

    Thanks for the post i’ve got some idea… my problem is if i use sp_spaceused ‘Backend’ it sent me 2.22GB of data size… but if i look at my each table size manually and sum it. it gave just 1.3GB.. Does the my database use an invisible table using my space that i may not see?

    Reply

Leave a Reply