How to remove unused space from SQL Server table?

bjp0bcyl  于 2023-05-21  发布在  SQL Server
关注(0)|答案(4)|浏览(112)

I've got a SQL Server table that had lots of data in a nvarchar(max) column. I set that columns value to all nulls so now the table should not take up much space. How can I reclaim that space?

I've tried DBCC SHRINKDATABASE and I've also tried backing up and restoring the database but the space seems not to be recovered. If I drop the table and recreate it with an insert/select type thing the space is recovered.

thanks

rqcrx0a6

rqcrx0a61#

I think you need DBCC CLEANTABLE , "Reclaims space from dropped variable-length columns in tables or indexed views."

6l7fqoea

6l7fqoea2#

What you are looking for is DBCC CLEANTABLE and should be used like

DBCC CLEANTABLE (DB_NAME,"TABLE_NAME", 0)
WITH NO_INFOMSGS;
GO

A Quote of warning from MSDN document

Best Practices

DBCC CLEANTABLE should not 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.

As Mark has already mentioned, you should refrain from doing so. It's not needed cause next time you insert data to those nvarchar(max) column it will use the space again. So, essentially you will not get any benefit out of it.

Take a look at this post for reasoning behind the same

reclaim-the-unused-space-from-sql-server-2005-database

8e2ybdfx

8e2ybdfx3#

Had similar problem, when needed to cleanup a number of DEV databases.

The best way that worked for me was first dropping the column, then running the clean table and then shrink database. And in the end re-create the column.

Alter table MyTable drop column MyColumn
DBCC CLEANTABLE(MyDB, MyTable, 0)
DBCC SHRINKDATABASE ('MyDB')
ALter table MyTable ADD MyColumn nvarchar(max) NULL

According to documentation: CLEANTABLE reclaims space after a variable-length column is dropped. SHRINKDATABASE operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

ncecgwcz

ncecgwcz4#

DBCC CLEANTABLE is a good solution but sometimes it's not possible to use DBCC commands due to permission. Another solution (thas also require some permissions) is to recompress the table partition(s).

This command reduced drastically the unused space:

alter table [shema_name].[table_name] rebuild partition = all with (data_compression = page)

It also reduce the disk space with data_compression.

If you do not want compression you can also use:

alter table [shema_name].[table_name] rebuild partition = all with (data_compression = none)

相关问题