First published on MSDN on Jun 18, 2008
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.
-
Shrink setups a GAM scan for the file to be shrunk. Starting at the beginning of the file pages are read ... 1, 2, 3, ....
-
As each page is processed the page type is used to determine the necessary data movement behavior. For data pages this results in a delete and insert pairing to move the row to another file so EMPTYFILE can complete.
-
When a BLOB page is encountered I thought that each fragment would be moved to a new page on another file. This does happen but not as I envisioned. Because the BLOB fragments are not doubly linked, a broader scan is required to locate the root of the chains that have fragments that need to be moved occurs.
-
When a BLOB page is located a new scan is established. The new scan is an IAM scan over the TEXT/IMAGE (BLOB) chain for the object found on the original page to be moved.
-
Each page in the IAM chain is read and each fragment on the page is reviewed.
-
Any fragment found that points to a fragment on the file to be EMPTIED is identified and the associated BLOB chain moved.
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
