CODEWITHSUNDEEP
sql-server
Peter Kellner
Peter Kellner

Reputation: 15488

How to remove unused space from SQL Server table?

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

Upvotes: 2

Views: 33237

Answers (4)

user10475133
user10475133

Reputation: 19

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)

Upvotes: 0

Alex M
Alex M

Reputation: 2548

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.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

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

Upvotes: 1

SqlACID
SqlACID

Reputation: 4014

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

Upvotes: 1

Related Questions