Here is the most debated Interview Question – Is Shrinking Database Good or Bad?
I will try to answer this in a single statement – “Shrinking Database is bad practice for performance as it increases fragmentation. It should be used in rare cases of running out of space on drive.”
I believe above statement is not enough to explain the various details associated with it. Here are few reference blog posts, I strongly suggest to read with regards to this subject.
- SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
- SQL SERVER – What the Business Says Is Not What the Business Wants
- SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
- SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters
- SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server
- SQL SERVER – Shrinking NDF and MDF Files – Readers’ Opinion
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Pinal Dave,
For Large Databases, I would understand that Shrink is not a best practice while It is time consuming as well….
But for small databases, following the below sequence wouldn’t be a bad idea……
1. Shrink Database
2. Rebuild Index
3. Update Stats
Why do you want to shrink? rebuild would again consume log space.
Thank you for responding….You’re articles/scripts are always helpful…. :)
Coming back to the idea of Shrinking files…..
I would prefer to shrink very rarely, while I’m running out of disk space.
or
When a large Purge occurs on the database, I would prefer to Shrink, Rebuild Indexes and Update Stats.
I totally understand it doesn’t help to clear space if we are planning to rebuild, but my point here is – Shrink wouldn’t be a bad idea if we follow the above three steps…
I understand the time factor, but however It would be okay as long as there is no harm to the database…….
To conclude, I would say – Shrink isn’t a bad practice, but just waste of time….
KrishV – Not everyone is as smart as you. Since you know what shrink does, you are free to make your decision based on situation. :)