By: Nitansh Agarwal | 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:
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.
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%.
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.
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.
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)))
ENDIf 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.
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.
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.
About the author
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
