DBCC SHRINKFILE operation increases data usage instead

Question

First of all, I know it's better not shrink database. But in our situation we had to shrink the data file to claim more space back.

Environment: SQL Server 2005 x64 SP3 Ent running on Windows Server 2003 Enterprise x64.

The database has one single data file and one log file. Before we run DBCC SHRINKFILE, the data file has 640GB, in which 400GB is free, so the data is about 240GB. To speed up the shrink process,we had to defrag the database first then we shrink data file.

However, after we shrinked the database data file using DBCC SHRINKFILE, the data changed to 490GB. How could it happen?

Answer 1

I asked around include Paul Randal. Here's the possible reason: When I rebuild indexes for those indexes that were dropped, the indexes would not physically removed from data file, they would be put in deferred drop queue, instead they would stay there and would be dropped in batch.