How It Works: More on DBCC Shrink* Activities

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*.