Microsoft – SQLServer – Database \ Datafile Management – Data\Log File Shrinking
Sometimes one ends with MS SQL Server Log \ Data files growing quite a bit.
This is especially true with log files.
The reasons for big log files are a bit plentiful:
- Un-committed Transactions
- Database Mirroring partner in-admissibility
Well, once the original reason why the database data\log file got so big is addressed; you may now be emboldened to shrink the data\log files.
What to do, how to address ?
You can try using dbcc shrinkfile.
Over the years, MS has done quite a bit of Engineering on it.
Some of those enhancements are brought to light by Bob Dorr. At the time of his Blog
posting, Bob worked has a “Senior SQL Server Escalation Engineer” in the “Microsoft
Customer Service and Support (CSS) SQL Escalation Services” group.
The areas that were most relevant to the issue that I was addressing (at the time) were:
1) Increase DBA visibility. As DBCC Shrink works it avails its status via the sys.dm_exec_requests; specifically the command column
a] DbccSpaceReclaim – Clean up deferred allocations and purge empty extents
preparing for data moves.
b] DbccFilesCompact – Move pages beyond the target to before the target and
truncate file as required.
c] DbccLOBCompact – Compacting the LOB data
2) Progress data is also visible in the same DMV (sys.dm_exec_requests). The
columns\attributes shown are percent_complete, estimated_completion_time,
3) The shrinkage is performed & committed in batches. This is beneficial in terms of if
you are a bit queasy or frustrated and terminated your work, you do not lose everything.
The current unit of work is 32 pages.
As named transactions are used, you can follow along by querying the
4) As “DBCC Shrinkfile” is a physical operation, it directly interacts with the Hardware;
specifically the Storage Sub-system. As it moves data around, other operations are quite
impacted and so if possible suspend other I/O “aggressive” operations.
5) Also, DBCC Shrinkfile is single-threaded and non-reentrant. That is for each database,
only a single dbcc shrinkfile operation can be on-going at any one time.
1) How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000