Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – What are the Different Ways to Script Procedure with T-SQL
Next Post
SQL SERVER – Viewing User Configurable Info in SQL Server

Related Posts

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

    Reply
  • 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….

    Reply
    • KrishV – Not everyone is as smart as you. Since you know what shrink does, you are free to make your decision based on situation. :)

      Reply

Leave a Reply

Menu