Edit

DBCC CLEANTABLE (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Reclaims space from dropped variable-length columns in tables or indexed views.

Transact-SQL syntax conventions

Syntax

DBCC CLEANTABLE
(
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , batch_size ]
)
[ WITH NO_INFOMSGS ]

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

database_name | database_id | 0

The database in which the table to be cleaned belongs. If 0 is specified, the current database is used. Database names must follow the rules for identifiers.

table_name | table_id | view_name | view_id

The table or indexed view to be cleaned.

batch_size

The number of rows processed per transaction. If not specified, the default value is 1000.

A value of 0 is unsupported and not recommended, to avoid a long recovery period.

WITH NO_INFOMSGS

Suppresses all informational messages.

Remarks

DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command doesn't reclaim space after a fixed-length column is dropped.

If the dropped columns were stored in-row, DBCC CLEANTABLE reclaims space from the IN_ROW_DATA allocation unit of the table. If the columns were stored off-row, space is reclaimed from either the ROW_OVERFLOW_DATA or the LOB_DATA allocation unit depending on the data type of the dropped column. If reclaiming space from a ROW_OVERFLOW_DATA or LOB_DATA page results in an empty page, DBCC CLEANTABLE removes the page.

DBCC CLEANTABLE runs as one or more transactions. If a batch size isn't specified, the default size is 1000. For some large tables, the length of the single transaction and the log space required might be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE can't be run as a transaction inside another transaction.

This operation is fully logged.

DBCC CLEANTABLE isn't supported for use on system tables, temporary tables, or the memory-optimized columnstore index portion of a table.

Best practices

DBCC CLEANTABLE shouldn't be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.

Result set

DBCC CLEANTABLE returns:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

Caller must own the table or indexed view, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Use DBCC CLEANTABLE to reclaim space

The following example executes DBCC CLEANTABLE for the Production.Document table in the AdventureWorks2022 sample database.

DBCC CLEANTABLE (AdventureWorks2022, 'Production.Document', 1000)
WITH NO_INFOMSGS;
GO

B. Use DBCC CLEANTABLE and verify results

The following example creates and populates a table with several variable-length columns. Two of the columns are then dropped and DBCC CLEANTABLE is run to reclaim the unused space. A query is run to verify the page counts and space used values before and after the DBCC CLEANTABLE command is executed.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.CleanTableTest', 'U') IS NOT NULL
    DROP TABLE dbo.CleanTableTest;
GO

CREATE TABLE dbo.CleanTableTest (
    FileName NVARCHAR(4000),
    DocumentSummary NVARCHAR(MAX),
    Document VARBINARY(MAX)
);
GO

-- Populate the table with data from the Production.Document table.
INSERT INTO dbo.CleanTableTest
SELECT REPLICATE(FileName, 1000),
    DocumentSummary,
    Document
FROM Production.Document;
GO

-- Verify the current page counts and average space used in the dbo.CleanTableTest table.
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.CleanTableTest');

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, NULL, NULL, 'Detailed');
GO

-- Drop two variable-length columns from the table.
ALTER TABLE dbo.CleanTableTest
DROP COLUMN FileName, Document;
GO

-- Verify the page counts and average space used in the dbo.CleanTableTest table
-- Notice that the values have not changed.
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.CleanTableTest');

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, NULL, NULL, 'Detailed');
GO

-- Run DBCC CLEANTABLE.
DBCC CLEANTABLE (AdventureWorks2022, 'dbo.CleanTableTest');
GO

-- Verify the values in the dbo.CleanTableTest table after the DBCC CLEANTABLE command.
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.CleanTableTest');

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, NULL, NULL, 'Detailed');
GO

Related content