I have a multiple TB database and I have been doing some clean-up and dropped many tables. So now the data files are half empty. If I don't care about releasing the space to the operating system, is there any other reason to shrink the files? I am thinking that now at least I do not need to worry about auto-growth settings, which might slow things down if I add a large table with ETL.


No. Unless you need the space for something else it is not a problem at all. The unused space won't cause any extra CPU or memory use, empty pages won't consume space in backups, etc... As data grows in future it will use the empty allocated space instead of requesting more from the filesystem.

In fact shrinking can cause issues such as extra fragmentation, that you may end up spending time rebuilding indexes to resolve, so should be avoided unless needed.

  • It might affect scans if the tables are not contiguous/fragmented, but it’s unlikely that all the free space is in gaps. – eckes Apr 16 at 21:17
  • What about restores from backups? I think that might be affected if the data files are bigger even though they are half empty... – xhr489 Apr 17 at 14:15
  • 1
    @xhr489 - if you have instant file initialization enabled (see docs.microsoft.com/en-us/sql/relational-databases/databases/… for details) it shouldn't affect restores either. – David Spillett Apr 17 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.