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.
SQL Server DBCC SHRINKDATABASE – Pros and Cons
sql server
Related Question
- Sql-server – Reorganize and Rebuild Indexes, Pros and Cons
- Sql-server – ReportServerTempDB in AlwaysOn Availability group
- SQL Server – Pros and Cons of Read Committed Snapshot Isolation vs Read Committed
- SQL Server – Questions on Availability Group Readable Secondary
- SQL Server – Deadlock Graph Interpretation and Solutions
Best Answer
As you know
DBCC SHRINKDATABASE
attempts to shrink both data and log files.Personally, I would probably never use
SHRINKDATABASE
. Instead I'd useDBCC 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 withEMPTYFILE
.In a properly configured database, you should never need to use either
SHRINKDATABASE
orSHRINKFILE
. 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?