Suppose you have a really large database and changes have been made by development so that the application is more efficient with the way it stores the data. Sufficient time has passed that all the old inefficiently stored data has been purged and you want to get some of that disk space back. You run DBCC Shrinkfile but your database isn’t shrinking. Well, if you have a lot of LOB data, it won’t so, how can you get that empty space out of your database?
1. Locate the tables that have lots of empty space
2. SELECT INTO newtable
3. DROP oldtable
4. Rename newtable to oldtable name
Now the DBCC Shrinkfile will release that free space back to the operating system
*Shrinking the database causes massive index fragmentation. The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.