DBCC ShrinkDatabase – I want to shrink my database.
TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it.
This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file.
Not shrinking your database is one of the more counter intuitive things out there. You might think that a smaller database is a good thing, however there are some negative side effect if you shrink your database regularly, or have the autoshrink option enabled. Side effects of shrinking your database include:
- Excessive I/O due to the shrink.
- Index fragmentation (most likely all of your indexes).
- Excessive I/O to defragment your indexes.
- After the shrink is complete, inserting or updating rows that require more space in your database will be slowed due to the time involved with growing your data file.
Read More »DBCC ShrinkDatabase – I want to shrink my database.