Thursday, April 7, 2011

Clean unused space when a variable-length column is dropped from a SQL Server table or indexed view

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. 

--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 



Script to test the free space

-- 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') 

  
--script 3
ALTER TABLE Testfreespace DROP COLUMN Column3 

when we run the script 3 the table free space for the variable length column has not been released. 


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 4
DBCC CLEANTABLE (Test,"Testfreespace")
 
 
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.  

FYI
  • 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. 
Resource:
http://www.mssqltips.com/tip.asp?tip=2348

No comments:

Post a Comment