Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 12:56

This question came from our site for professional and enthusiast programmers.

6  
Why do you care? Are there prying eyes on your backup files? –  Michael Petrotta Feb 28 '11 at 0:54
1  
Data pages are 8KB not 1024 KB. I'm not sure that any of the solutions you think work do actually work. The pages might no longer be allocated to that object but I'm not sure that they are zeroed out. –  Martin Smith Feb 28 '11 at 0:58
    
What is the value are you after here? Backup/restore is optimized for maximum throughput without disturbing the current indexes etc. Rearranging data on disk for maximum compactness is without doubt not worth the cost of doing the rearrangement since the very next write will invalidate the benefit. –  stephbu Feb 28 '11 at 1:08
    
The sp_clean_db% sprocs can force cleanup, but they will just zero the dead data, not compact the structure. –  stephbu Feb 28 '11 at 1:14
    
There are prying eyes indeed! The data contains sensitive information that needs to be removed from the server. eg. If a file scanner scans the backup file it should not find any data, especially data that doesn't actually exist in the table. –  Schotime Mar 6 '11 at 2:09

2 Answers 2

--Thanks for the advice. The only way to fix the issue was to do the following: -- 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 -- 4. then run this t-sql:

declare @dbexec varchar(100) set @dbexec = 'sp_clean_db_free_space ''' + DB_NAME() +'''' exec(@dbexec) go -- Do this for all tables that require removing the ghost records declare @dbexec varchar(100) set @dbexec = 'DBCC CLEANTABLE (''' + DB_NAME() + ''', ''TABLENAME'')' exec(@dbexec) go dbcc shrinkdatabase (0,0,NOTRUNCATE) go checkpoint go --This worked!!

share|improve this 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.
share|improve this answer
    
Thanks for the advice. The problem is that the only way to fix the issue was to do the following: –  Throbbo Mar 21 '11 at 4:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.