Now, don’t confuse shrinking the transaction log with shrinking data files. Shrinking the log may be necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform.

In either case, I’m not talking about using the TRUNCATEONLY option – all that does is lop off any unused space at the end of the files – that’s perfectly fine. I’m talking about actually running the shrink algorithm.

Shrinking of data files should be performed even more rarely, if at all. Here’s why: data file shrink can cause *massive* index fragmentation (of the out-of-order pages kind, not the wasted-space kind) and it is very expensive (in terms of I/O, locking, transaction log generation).

The same code is used for DBCC SHRINKFILEDBCC SHRINKDATABASE, and auto-shrink – they’re equally as bad. As well as introducing index fragmentation, data file shrink also generates a lot of I/O, uses a lot of CPU, and generates *loads* of transaction log – as everything it does is fully logged.

Bottom line – try to avoid running data-file shrink at all costs! I’m often misquoted as saying ‘never run data-file shrink’ – that’s not true. I say ‘never run a regular shrink operation of any kind’, and if you absolutely must run a data-file shrink, be aware of the problems it can cause.


Last modified: October 19, 2021



Write a Reply or Comment