Why shouldn’t I shrink my database data file?

Shrinkage Hurts
Shrinkage Hurts
A while back I did a post about why you shouldn’t shrink your data file. This one is going to be similar in some ways, different in others.

TL;DR: It’s pretty pointless and will almost certainly cause performance issues.

Let’s start by asking why you might want to shrink your data file.

It’s too big

I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as big as it is. This also goes in with the I need to free up disk space with no good reason why the space needs to be freed up.

There are good reasons to shrink the data files and they do revolve around space. For example:

  • We just archived/deleted a large amount of data that we don’t plan on replacing.
  • We planned for a certain amount of growth and it just isn’t happening.
  • We’ve redesigned the database and some of the data that used to reside in this file lives somewhere else now. (New filegroup, adding multiple data files etc.)
    It helps performance

    I’ve heard people say performance but that’s always been one of those magical “I had a problem and this fixed it” or “I’ve been told it helps” type things without any proof behind them. I honestly can’t think of any way a smaller data file is going to help performance. (It can help with restore time but let’s hope that’s unusual and it probably isn’t worth the cost.)

    Continue reading on SQLStudies.com.

    Continue reading on SQLStudies.com.

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *