Since SQL Server 2005 there is an additional shrink logic to
compress TEXT/IMAGE data, referred to as LOBs(Large Object's). 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
which are indicated in the sys.dm_exec_requests dmv in command column along
with the progress indications
select percent_complete,
estimated_completion_time, cpu_time, total_elapsed_time, * from
sys.dm_exec_requests.
Now let’s see the phases that SHRINK operation
…
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 750MB, it starts from there
MDF
File
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 (BOF-Beginning Of File). Sets up the necessary information
to tell the allocation routines that, no pages should be allocated beyond 750MB (Target
Size). 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 be used for this purpose but need to be aware of the large
volume of output it produces. Once all pages are moved below the target
point of truncation, LOB's will be compacted unless we have specified truncate only in our 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 perform.
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.
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 starts. Previously during shrink it keeps a
transaction open for a long time and caused the log files to grow because you
can’t truncate past an active transaction. This new process of processing in batches of 32 pages also makes shrink restart
capable. If you cancel/kill a shrink operation only the current active
transaction is rolled back. So you could schedule a shrink
operation during a maintenance window and limit its run time 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‘ and by Using sys.dm_tran_active_transactions
you can view the activity.
Move LOB Fragement
Like all other data, when a
LOB fragment is found inside the shrinking line it is also moved below the
target. If the LOB chain is in an older SQL Server format the entire chain
is upgraded to current SQL Server LOB format by 'UpgradeBlob' getting invoked. The fastest way to upgrade
the LOB chains is by a series of BULK export and BULK insert operations.
Otherwise expect the shrink activity to require more resources and time to
complete if it requires LOB compaction. There is an excellent post from PAUL on
this Why LOB data makes shrink run slooooowly ?
Single Threaded
Multiple shrink operations against the same file/database are not
allowed. You will encounter an error as stated below. The shrink operation is currently a single
threaded operation. The shrink does not run in parallel unlike DBCC CHECKDB
Error: File ID 1 of database ID 10 cannot be shrunk as it is either
being shrunk by another process or is empty
.
Effects of Shrinking
1. I/O
contention
2. Transaction
Log file space
3. Fragmentation
4. Lock
Contention and many other things,,, :)
Paul has mentioned this in an excellent way here.
Moral Of Story
Bottom line is that there are lot of overheads involved in Data file
Shrinking and hence it is not recommended as a good practice Unless you have no
other option and need space. The information's mentioned in this blog has contents collected
from blogs written by CSS Engineers
Cheers... Have a Gr8 day!!!
