My peers are starting to tease me about becoming a dbcc shrink* expert. (Ha, Ha I said.) Then, I uncovered another design facet about shrinkfile helping with a SQL Server 2000 case.
The situation: dbcc shrinkfile, with EMPTYFILE, against a file with only 128 pages was taking hours to complete. In the end it is by design but it was not a design I anticipated.
Here is what is taking place during the shrink of this file.
So to move a BLOB page may require the entire blob chain to be scanned. In this customers case they have a 1TB database and this BLOB index was ~500GB. So to move the BLOB page lots of I/O and scanning take place.
I have not studied the behavior of SQL Server 2005 in regards to this behavior.
You can read more about this subject from my previous posts about shrink*.
http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-t...
http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-ta...
References: shrinkfile, shrinkdb, shrinkdatabase
Bob Dorr
SQL Server Senior Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.