How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000

SQL Server 2005 adds additional shrink logic to compress TEXT/IMAGE data, referred to as LOBs. The dbcc shrink* code uses the same underlying methods that ALTER INDEX .... WITH (LOB_COMPACTION ON) uses to compact the LOB space in the database files.  

There are various stages of dbcc shrink* activity. These stages are indicated in the sys.dm_exec_requests command column along with the progress indications.

 

Step	Command	Description
1	DbccSpaceReclaim	Clean up deferred allocations and purge empty extents preparing for data moves.
2	DbccFilesCompact	Moves pages beyond the target to before the target and truncate file as required.
3	DbccLOBCompact	Compacting the LOB data.

 

A target extent is calculated based on the information supplied to the shrink* command. If you have a file that is 1GB in size and you specify a target of 75%. The target extent start at 750MB.   

 

Shrink Steps

· Cleans up space that is available in the entire file, such as deferred allocations. This helps expose space in the lower portion of the file.

· Sets up the necessary information to tell the allocation routines that no pages should be allocated beyond 750MB.

· Scans all pages from 750MB to 1GB that are marked allocated in the GAM. Data on allocated pages is moved to the lower portion of the file. This can involve index, LOB chain and other updates to move the row. The more rows that are found beyond the target extent location the more work shrink has to do. Do not compare one file to another because the data layout is critical to understanding the work that shrink has to perform. The dbcc extentinfo command can provide insight into this work but use this with care as the command can produce a large amount of output.

· Once all pages are moved below the truncation target LOBs will be compacted unless truncate only was specified in the shrink* command.    

WARNING: This should only be used under the guidance of SQL Server support and may not be supported in the future.  

SQL Server 2005 has a trace flag –T2548 dbcc tracon(-1, 2548) that allows shrink* and other LOB_COMPACTION actions to be skipped. Enabling the trace flag returns shrink* behavior to that similar to SQL Server 2000.  

Using ALTER INDEX … LOB_COMPACTION is another way of limiting the work that shrink* has to do. If the indexes are maintained under tight LOB_COMPACTION the shrink work is limited.

 

Auto Shrink

 

Auto Shrink avoids the LOB compaction step because the LOB compaction is an intensive and can be a lengthy operation.

 

Progress Indication

 

The progress indication in sys.dm_exec_requests is based on a grouping of work for DBCC. When a dbcc command starts it performs an estimate of the work it will be doing. Going back to the 750MB example part of the estimate will be that the 250MB at the end of the file has to be processed. As each page is interrogated and moved the progress indication is advanced. However, this is not the only progress indication calculation that makes up the progress and estimated time indicators. For the same shrink an estimate at LOB_COMPACTION is made. This is included in the progress indicators. Combining the progress indicators with the command value should help you follow the dbcc progress.

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests

 

Shrink Transaction Behavior

 

Shrink works in transaction batches of ~32 pages. Once 32 pages are moved below the truncation point the transaction is committed and a new transaction started. This avoids an older issue with shrink* that kept a transaction open for a long time and caused the log files to grow because you can’t truncate past an active transaction. It also makes shrink* restart capable. If you cancel/kill a shrink* operation only the current active transaction is rolled back. This means you could schedule a shrink* operation during a maintenance window and limit its runtime to that maintenance window. You can restart it during the next maintenance window and it will pick up from where it left off. The only disadvantage to this is that allocations could occur after the target once the shrink is terminated. This may require shrink to do additional work for newly allocated space but this should be reasonably limited.  

The shrink* process uses named transactions. For instance, during the DbccSpaceReclaim command you will see transaction names such as ‘DeferredAllocUnitDrop::ReclaimSp‘. Using sys.dm_tran_active_transactions you can view the activity as another progress indication as well.  

select * from sys.dm_tran_active_transactions  

Move LOB Fragement

 

Like all other data, when a LOB fragment is encountered the fragment is moved below the target.   If the LOB chain is in SQL Server 2000 format the entire chain is upgraded to SQL Server 2005 LOB format.   LOB data that was stored under SQL Server 2000 and has not been is still on SQL Server 2000 format.  

When shrink encounters one of these chains 'UpgradeBlob' is invoked.   This copies out all bytes of the 'entire' LOB chain out and and inserts it back in SQL Server 2005.   This requires additional overhead and can elongate the shrink* activity.  

You can't use ALTER INDEX ... LOB_COMPACTION or REORGANIZE and you can't use update set tData = tData to upgrade the LOB chains.   Neither of the actions require physical modifications so the LOB chains are not modified.    The fastest way I have found to upgrade the LOB chains is a series of BULK export and BULK insert operations.  Otherwise expect the shrink* activity to require more resources and time to complete.

Other Affects on Shrink

 

Shrinking a busy file can also result in lock contention while the rows are moved. Monitoring the locking activity and wait statistics information can be helpful. Severe waits may result in SQL Server error log messages from the shrink process. 

Example: “DBCC SHRINKFILE for file ID %d is waiting for the snapshot transaction with timestamp %I64d and other snapshot transactions linked to timestamp %I64d or with timestamps older than %I64d to finish.” SQL Server Books Online topic ‘dbcc shrinkfile’ provides extended details about shrink and snapshot isolation. 

Understand the I/O subsystem. Shrink is moving around page data which involved reading and writing of the page data. Subsystems that are not responding well impact the performance of the shrink operation as well.

 

Single Threaded

The shrink operation is currently a single threaded operation. The shrink does not run in parallel like dbcc checkdb might. I had an idea that using multiple connections to issue a shrink* command would allow parallel like activity. Multiple shrink operations against the same file/database is not allowed.   

Error: File ID 1 of database ID 10 cannot be shrunk as it is either being shrunk by another process or is empty.

 

Bob Dorr
Senior SQL Server Escalation Engineer

Comments

• Anonymous
  April 19, 2008
  PingBack from http://www.travel-hilarity.com/travel-airline-tickets/?p=910

• Anonymous
  June 18, 2008
  My peers are starting to tease me about becoming a dbcc shrink* expert. (Ha, Ha I said.)  Then,

• Anonymous
  April 28, 2010
  If i stop shrink command before its completion, does it rollback everything ?

• Anonymous
  June 23, 2010
  Hello! I have table which (based on data from sys.allocation_units) has total MB = 45 GB, used = 13 GB, Data = 400 MB and LOB = 13 GB. I tried to recover the 32 GB of unused space by using "Alter index rebuild" and "alter index reorg with lob_compaction"...no changes I did then copied the data with a select * into and... Total MB = 5 GB, Used = 5 GB, Data= 300, LOB = 5 GB Any suggestion to obtain same results without copying the data (by select into or BCP out/in) ? Thanks!!

• Anonymous
  January 23, 2012
  For avoiding the below error : Error: File ID 1 of database ID 10 cannot be shrunk as it is either being shrunk by another process or is empty. Solution : Increase the Initial size of database by just 1 MB and give OK . Try after increasing, the error will disappear.

• Anonymous
  September 05, 2012
  Can someone truly answer whether or not you can stop an EMPTYFILE operation and what are the affects if you do?  Can you start it back up and have it pick up where you left off and does the file remain open and usable until it is fully empty? Thank you.

• Anonymous
  July 14, 2013
  Hi I'm having the following situation. FG1 with 22 files, FG2 with 12 files. I need to empty 12 files from FG1 and 7 from FG2. Can I run 2 DBCC SHRINKFILE(name, EMPTYFILE): 1 against each file of interest from FG1 and the other against each file of interest from FG2 ? Reading one of the last paragraphs in the article makes me believe I cannot do it. "Multiple shrink operations against the same file/database is not allowed. " Or can I ? Thanks, Alin