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
4条答案
按热度按时间rqcrx0a61#
I think you need DBCC CLEANTABLE , "Reclaims space from dropped variable-length columns in tables or indexed views."
6l7fqoea2#
What you are looking for is DBCC CLEANTABLE and should be used like
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
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.
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.
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:
It also reduce the disk space with data_compression.
If you do not want compression you can also use: