Skip to content

Latest commit

 

History

History
191 lines (139 loc) · 6.78 KB

dbcc-cleantable-transact-sql.md

File metadata and controls

191 lines (139 loc) · 6.78 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
DBCC CLEANTABLE (Transact-SQL)
DBCC CLEANTABLE reclaims space from dropped variable-length columns in tables or indexed views.
rwestMSFT
randolphwest
03/04/2024
sql
t-sql
reference
CLEANTABLE_TSQL
DBCC_CLEANTABLE_TSQL
DBCC CLEANTABLE
CLEANTABLE
disk space [SQL Server], reclaiming
reclaiming space
reallocating space
removing columns
DBCC CLEANTABLE statement
space [SQL Server], reclaiming
deleting columns
dropping columns
TSQL

DBCC CLEANTABLE (Transact-SQL)

[!INCLUDE SQL Server SQL Database Azure SQL Managed Instance]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 ]

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 [!INCLUDE ssSampleDBobject] 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