In a recent post, I demonstrated how SQL Server will reuse space made available when rows are deleted from a table. This occurs automatically without any outside intervention on our part.

However under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.

To illustrate this behavior and the aforementioned DBCC utility, let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.

USE tempdb ;
GO 

--create a test table
CREATE TABLE dbo.Test
        (
         col1 INT
        ,col2 VARCHAR(50)
        ,col3 VARCHAR(MAX)
        ) ; 

--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
        BEGIN
                SELECT
                        @cnt = @cnt + 1 ;
                INSERT
                        dbo.Test ( col1,col2,col3 )
                VALUES  (
                         @cnt
                        ,'test row # ' + CAST(@cnt AS VARCHAR(100))
                        ,REPLICATE('A',@cnt)
                        ) ;
                IF @cnt = 1000 
                        BREAK ;
        END 

Now let's view the table to make sure we have what we think we have.

--view the table 
SELECT
        *
FROM
        dbo.Test ;

Using a Dynamic Management View, let's see how much space our newly created table is consuming. Note that since this script employs the use of a DMV, it will not work with SQL Server 2000.

--check the size of the table 
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
             DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL,'Detailed') ; 

On my system, it indicates that 84 data pages are used to store the 1000 rows. Each data page is, on average, 78.4% full.

Now let's drop the third column, the one that consumes the most space, and check the space used once again.

--drop the last column
ALTER TABLE dbo.Test DROP COLUMN col3 ; 

--check the space used again
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
             DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ; 

We get the same results - 84 data pages, storing 1000 rows, each 78.4% full - even after dropping the column that consumed the most space.

Now, let's reclaim the space using the DBCC CLEANTABLE command and recheck the space consumed.

--reclaim the space from the table
DBCC CLEANTABLE('tempdb', 'dbo.Test') ; 

--check the space used one more time
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
             DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ; 

This time, considerable less space is consumed; the average page is only filled 4.5% full!

Finally let's clean up.

--clean up
DROP TABLE dbo.Test ; 

For more information, check out DBCC CLEANTABLE in Books Online.

Cheers!

Joe