Sql-server – How to truly DELETE data from a SQL Server table – still shows up in notepad in backup file

optimizationsql server

SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record per page. This will however increase the size of your database substantially.

So far I have tried the following without complete success, whereby they will fix the problem a little, reduce some of the DELETED record data from within the pages, though not all.

ALTER INDEX...WITH REBUILD  
DBCC SHRINKFILE using NOTRUNCATE can compact pages into  
dbcc shrinkdatabase (0,0,NOTRUNCATE)  
sp_clean_db_free_space 'DBNAME'

This does not work and the only way I have found to fix this problem is to:

  1. Insert all data into a temp table
  2. DROP the table Reinsert the data back and recreate all indexes,
    triggers, constraints etc etc
  3. drop Temp table

Any better ideas out there?

Best Answer

The problems with your approach:

  1. if DELETE compacted the pages immediately, it will cost more I/O than it is worth. The point of RDBMS is to be fast. It will also not help fillfactors and b-tree balancing (indexes)
  2. not mentioned, but similar - if DROPping columns really removed data immediately, it would require a large amount of I/O to completely restructure the table
  3. DBCC Shrinkfile + NOTRUNCATE is already a brutal operation, it shuffles all used pages from the end of the file towards the front. To have it move data within pages is even more expensive and would be a very bad idea, since you will end up with FILLFACTOR of 100%, which is very rarely a good ideal at all.
  4. sp_clean_db_free_space which you have found is not risk-free, as Book Online clearly states. It is not something they envision sane DBAs to be running often (if even more frequently than once per lifetime), hence the warning "Before you run sp_clean_db_free_space, we recommend that you create a full database backup.", one of few procedures to carry it
  5. If your database is full-logged, the transaction log contains a copy of the data before you removed it, so you need 2 iterations of backup before the log (contained in the backup) will no longer contain the data removed.
Related Question