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:
- Insert all data into a temp table
- DROP the table Reinsert the data back and recreate all indexes, triggers, constraints etc etc
- drop Temp table
Any better ideas out there?