Clean unused space when a SQL Server table with a variable length column is dropped

By:   |   Updated: 2011-04-04   |   Comments (6)   |   Related: More > Database Administration


Problem

Whenever rows or columns are deleted from a table, SQL Server reuses space to accommodate new data. However, this is not the case when a variable length column is deleted. The space used by this type of column is not reclaimed automatically. Variable length columns can be of data types varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. In this tip, I have tried to put forth a solution to reclaim this space and this should work for SQL 2005 and later versions.

Solution

First, let's see if SQL Server actually reuses space after data is deleted from a row. We will create a table with an integer column, a fixed length char column which can hold 20 characters and a variable length varchar column that can hold up to 8000 characters and then we will populate the table with test data.

--script 1
--Create Table
CREATE TABLE testfreespace
( column1 INT
,column2 CHAR(20)
,column3 VARCHAR(8000) )

--Populate with test data
DECLARE @count INT ;
SET @count = 0 ;
WHILE @count < 3000
BEGIN
SELECT
@count = @count + 1 ;
INSERT into testfreespace VALUES ( @count,'test row # ' + CAST(@count AS VARCHAR(10)),REPLICATE('TestData', 3000)) ;
END

If we query the table we just created it should look something like this:

reclaiming the space when a variable length column is deleted in sql server 2005 and later versions

To check the space used by the table we will use DMV sys.dm_db_index_physical_stats. This returns the size and fragmentation information for the data and indexes of the specified table or view. This DMV accepts 5 parameters - database_id, object_id, index_id, partition_number and mode (specifies the scan level that is used to obtain statistics).

--script 2
SELECT
alloc_unit_type_desc,
page_count,
avg_page_space_used_in_percent,
record_count
FROM sys.dm_db_index_physical_stats( DB_ID('Test'),OBJECT_ID(N'Testfreespace'),NULL,NULL,'Detailed')

The results will look something like this. As you can see there are 3000 datapages used to store the data and on average each data page is 99% filled.

use dmv sys.dm_db_index_physical_stats

We will now delete half the rows to see if the space is released.

--script 3
DELETE FROM Testfreespace WHERE column1 >= 1 and column1 <= 1500

We are now left with 1500 rows. Let's see how many pages are occupied now if we run script 2 again. It's interesting to see that the pages are the same however the average page space used is reduced from 99% to 49%.

run script again to see how many pages are occupied

Let's now use script 1 and insert 1500 rows this time to see if the space is reused. If we run script 2 again, we can use sys.dm_db_index_physical_stats DMV to see if the space is reused or not. As expected SQL Server reuses the free space in the pages and now the percentage used has risen from 49% to 99% and the number of pages used increased to 3008.

as expected sql server reuses the free space

Now to address our main topic, that the space is not immediately released when variable length column is dropped, I will drop the third column which is of type varchar and is consuming most of the space.

--script 4
ALTER TABLE Testfreespace DROP COLUMN Column3

If we rerun script 2 again, we can see that the space has not been released.

drop the thrid column which is of type varchar

Now let's insert 500 more rows to the table (since there are only two columns we will populate just these 2 columns only) and then see how SQL Server uses the free space.

--script 5
DECLARE @i INT;
SET @i = 0 ;
WHILE @i < 500
BEGIN
SELECT
@i = @i + 1 ;
INSERT into Testfreespace values(@i, 'Row num' + CAST(@i AS VARCHAR(10)))
END

If we run script 2 again, we can see the data pages have increased even when there should be ample space to reuse, but SQL allocated more pages to accommodate this new data. This confirms that the space is not reused after we dropped a variable length column.

sql server allocated more pages to accommodate this new data

In order to reclaim this space we have to use DBCC CLEANTABLE command.

Syntax: DBCC CLEANTABLE( Databasename, "Table_name or View_name")

In our case the command would be as follow.

-- script 6
DBCC CLEANTABLE (Test,"Testfreespace")

Let's run script 2 again and check the space used. Wow, although the number of datapages remains the same, the average percentage occupied has dropped from 99% to a meager 0.5%. This free space can very well accommodate thousands of new rows. To check this, run script 5 again and then run script 2 again to see the difference. The percentage used will increase only slightly.

run a dbcc cleantable command

Note: During the CLEANTABLE task the table is exclusively locked and if the size of a column is pretty large it might take a considerable amount of time. Also, the minimum privilege to execute this command is to be a table owner. At the server level, only DDL admin and/or sysadmin can do so.

Next Steps
  • The same outcome can be achieved by rebuilding clustered index, but that maybe more resource intensive.
  • Developers can be suggested to use command after any variable length column is dropped in a table, this will optimize existing disk space.
  • For huge tables it might use a lot of transaction log space, so you may want to divide this into smaller batches.
  • DBCC CLEANTABLE is not supported on system tables or temporary tables.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nitansh Agarwal Nitansh Agarwal is a lead with 4+ years of extensive experience in database administration where he handles large critical databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-04-04

Comments For This Article




Tuesday, June 27, 2017 - 12:44:59 PM - SM Back To Top (58433)

 

 How would I check if the table have unused variable length column?


Thursday, April 7, 2011 - 3:27:09 AM - aasim abdullah Back To Top (13478)

Jonathan, one more correction. when you drop a column from your table. NO such action is performed (Column is not dropped physically) and only meta data is changed, so in future you can’t see or use it. No space is released and column still exists.

When you try to decrease length of fixed length column. It just make changes in meta data, that column values will be according to new mentioned length and NO PHYSICAL change occur. It means size is never deceased. For example if you want to change CHAR(15) column to CHAR(10). Sql Server will never decrease its length to 10. Column length will remain 15 physically and its only meta data which will bind you to use only 10 character length.

http://connectsql.blogspot.com/2010/12/sql-server-internal-negative-side.html


Monday, April 4, 2011 - 1:41:36 PM - Nitansh Back To Top (13430)

Jonathan, just a small correction. When I mentioned "For huge tables it might use a lot of transaction log space, so you may want to divide this into smaller batches" it actually means dividing the whole reclaim processes into small batches. The command will look something like this.

DBCC CLEANTABLE ('Test','Testfreespace', 10) - The 3rd parameter will determines the batch size or the no. of steps to reclaim space. If given as 0, it reclaims steps in a one go and transaction log will grow very fast. I was scratching my head whole evening and it strike me when I received the second question :)

Anu, since the size of table is huge the processes will take a considerable amount of time, I would suggest you to execute the command during off business hours or over the weekend. Also, divide the trsnaction into small batches to avoid filling your log file. Thanks for asking :)


Monday, April 4, 2011 - 12:43:09 PM - Anu Back To Top (13429)

Good Article but can we do above 200 GB table .

If it is possible without giving downtime.

 


Monday, April 4, 2011 - 10:26:31 AM - Nitansh Back To Top (13428)

Thanks for correction Jonathan. It's actually not possible to drop a column is batches.


Monday, April 4, 2011 - 9:51:21 AM - Jonathan Ausubel Back To Top (13427)

Good article, but I'm confused about just one thing.

Under "Next Steps", you recommend:

"For huge tables it might use a lot of transaction log space, so you may want to divide this into smaller batches".

How can you drop a column in smaller batches?















get free sql tips
agree to terms