1

I have read MSDN - Microsoft Manual according to this issue, but I still do not have clear understanding, when it is useful to apply this option and when it is not. Please advise.

2
  • 8
    Its never useful just dont use it. See what Paul Randal has to say about it. If you really need to shrink use dbcc shrinkfile
    – Shanky
    Feb 11 '16 at 9:02
  • 5
    Agreed with both comment and answer. This command is never useful. Period. Pretend you never learned it. Feb 11 '16 at 13:37
5

As you know DBCC SHRINKDATABASE attempts to shrink both data and log files.

Personally, I would probably never use SHRINKDATABASE. Instead I'd use DBCC SHRINKFILE, and even then would only use it on datafiles to resize them correctly, or on log files if I was trying to shrink them back in order to grow them out again to the correct number of virtual log files (VLFs). The other use case for datafiles (if you had multiple) would be if you were looking to remove them; you would first need to shrink with EMPTYFILE.

In a properly configured database, you should never need to use either SHRINKDATABASE or SHRINKFILE. You should have pre-allocated and sized your log and data files correctly, and set caps to avoid files growing out of control. Prevent the log file from needing to grow in the first place through correct sizing (to accommodate the longest possible running open transaction) and regular log backups.

For debate on VLF sizing please check out the SQLSkills article:

Transaction Log VLFs – too many or too few?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy