Use DBCC CLEANTABLE to reclaim space after you delete a variable-length column field

Source: Internet
Author: User

Tags: sql Server Reclaim Space Contraction Table original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated.

When SQL Server deletes a variable-length column or decreases the length of a variable-length column, the size of the table does not respond automatically, unless the DBA rebuilds the index or reorganized the index. The variable-length columns include varchar,nvarchar, varchar (max), nvarchar (max), varbinary, varbinary (max), text, Ntext,image, sql_variant, and XML.

SQL Server provides a dbcccleantable command to reclaim the space of a variable-length column that has been deleted in a table or indexed view.

Let's do a test here:

1. -- Create a test table

CREATE TABLE testfreespace

(Column1 INT

, Column2 CHAR(+)

, Column3 VARCHAR(8000))

2. -- Insert Data


SET @count = 0;

While @count <



@count = @count + 1;

INSERT into testfreespaceVALUES(@count,' Test row # '+CAST(@countasVARCHAR (Ten)),REPLICATE(' TestData ', +)) ;


3. -- See usage of space usage






From sys. Dm_db_index_physical_stats(db_id(' fndblogtest '),object_id(N ' Testfreespace '), Null,null,' detailed ')


Alloc_unit_type_desc Page_count avg_page_space_used_in_percent Record_count

-------------------------------------------------------------------------------- ------------------------------ - -------------------

In_row_data 99.2710649864097 3000

(1 row (s) affected)

4. --- Delete the variable length column Column3

ALTER tabletestfreespaceDROPCOLUMNColumn3

5. Using the 3 script query to see that avg_page_space_used_in_percent is still 99.2710649864097 unchanged.

6. -- execute DBCC cleantable

DBCC cleantable(fndblogtest, "Testfreespace")

7. Run the 3 script again to see the following results:

Alloc_unit_type_desc Page_count Avg_page_space_used_in_percentrecord_count

-------------------------------------------------------------------------------- ------------------------------ - -------------------

In_row_data 0.382999752903385

(1 row (s) affected)

You can see now that the avg_page_space_used_in_percent has changed and that the space has been released.

For more information, refer to:dbcccleantable (Transact-SQL)

This article is from the "Focus on SQL Server Technology" blog, so be sure to keep this source

Come from:

Use DBCC CLEANTABLE to reclaim space after you delete a variable-length column field

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.