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

Solution 1:

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

Solution 2:

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