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