1

If I have a table with an NVARCHAR(MAX) column and I drop it, the standard way to reclaim the space it used is to either:

  1. Rebuild the clustered index or
  2. Run DBCC CLEANTABLE

If I didn't perform either of these, the column still remains (visible via sys.system_internals_partition_columns) in the physical structure of the table, and did a full backup & restore of the database, would the restored copy still have dropped column in its physical structure and take up the same amount of space as before I had dropped the column?

For my own use case I am looking at SQL Server 2012, but I presume this functions the same across all relatively modern versions. I am aware this is easy to test, but I am hoping someone has more background knowlegde on this as I haven't been able to find much myself.

References:

  • 1
    A backup actually backs up physical pages that have content, and restores those exact same pages. It doesn't have any logic like don't include dropped columns on the other side, so if you have pages with other data still on them then yes those would be visible in the restored copy. As you suggest, easy to validate this. – Aaron Bertrand May 1 '18 at 17:46
1

If I have a table with an NVARCHAR(MAX) column and I drop it

I wrote about how you can use DBCC CLEANTABLE to reclaim the space.

Under the hood, sql server just deallocates the pages and then a background thread called ghost clean up will clean up the data.

Another aspect that I am assuming you are asking is physical data security - how can I be sure that my data is not recoverable once I delete/drop it ?

To answer that, SQL Server provides - sp_clean_db_free_space

in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_free_space to clean these ghost records.

The length of time required to run sp_clean_db_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running sp_clean_db_free_space can significantly affect I/O activity, we recommend that you run this procedure outside usual operation hours.

Now that being said, its really hard to destroy the data esp. for drop column

  • if your column data type is defined as fixed length e.g. char(10) (Index Rebuild is required ; for heap create a clustered index and then drop it as there is not other option)
  • if your column data type is defined as variable length e.g. varchar(10) / nvarchar(10) (which can be taken care by dbcc cleantable)

Michael J. Swart has a very interesting post on It’s Hard To Destroy Data - esp the summary table with different options.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.