Jaa

How It Works: More on DBCC Shrink* Activities

  • Artikkeli

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

https://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

https://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular.aspx

References: shrinkfile, shrinkdb, shrinkdatabase

Bob Dorr
SQL Server Senior Escalation Engineer

Comments