Reclaiming Table Space

Reclaiming Table Space

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

--create a test table

CREATE TABLE dbo.Test

        (

         col1 INT,

         col2 VARCHAR(50),

         col3 VARCHAR(MAX)

        ) ;

--Inserting Data

INSERT INTO dbo.Test (col1, col2, col3)

VALUES (1, 'AA', 'AAAA'),

       (1, 'BB', 'BBBB'),

       (1, 'CC', 'CCCC'),

       (1, 'DD', 'DDDD'),

       (1, 'EE', 'EEEE')……….. Needs Lot of Data to Entered
  

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

SELECT *

FROM   dbo.Test

Using a Dynamic Management View, let's see how much space our newly created table is consuming. 

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

alloc_unit_type_desc    : IN_ROW_DATA 
page_count        :84
avg_page_space_used_in_percent : 78.51964418087472  
record_count : 5000

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

ALTER TABLE dbo.Test

DROP COLUMN col3 ;
 
 

We get the same results - 84 data pages, storing 1000 rows, each 78.6% 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. 
Syntax:

DBCC CLEANTABLE

      ( { 'database' | database_id | 0 }

         , { 'table' | table_id | 'view' | view_id }

            [ , batch_size]

       ) [WITH NO_INFOMSGS]

Key:

    batch_size   - The no. of rows to process per transaction.

                       default (or if 0 is specified) = whole table

    NO_INFOMSGS  - Suppress all information messages.

If 0 is specified, the current database will be used.

DBCC CLEANTABLE('tempdb', 'dbo.Test')
 
This time, considerable less space is consumed; the average page is only filled 4.5% full!