0

This question already has an answer here:

I purged about 100 GB on 200 GB SQL Server v11 DB. The MDF file still show about 200 GB in the file explorer.

Everything looks fine, first tests shows 50% time treatments increase and the backup's size are reduced.

Do you think shrinking is still interesting ? If yes what could be the best way ?

  • Release unused space,
  • Reorganize pages before releasing unused space (shrink file to : X MB),
  • Empty file by migrating the data to other files in the same filegroup.

Index Reorganize/Rebuild, then Update Stats is planned anyway after this operation.

Thanks for help

marked as duplicate by Josh Darnell, MDCCL, hot2use, Community May 22 at 9:45

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    To free up disk space for how long, exactly? Will you no longer be adding any data to this database? If you shrink it now, and add data later, it's just going to have to grow again to accommodate the new space. So not sure what shrinking will accomplish, because you can't exactly do anything with all the space you freed up in the meantime. See this, this, and this. Lots of experts chiming in with similar opinions and plenty of links to further reading. – Aaron Bertrand May 21 at 15:23
  • Also if you shrink the database and then perform rebuilds, those operations will cause the database to grow again immediately. So again, what did you accomplish? – Aaron Bertrand May 21 at 15:27
  • A factor is whether you need the disk space before the database might grow again. If so, then you your highest priority should probably be adding more storage space so you don't have to be concerned about 100 GB. – Conrad S. May 21 at 17:12
  • Thanks for reply and sorry for duplicate, The database will grow to the same space in approximatively 4/5 years. And freeing up this space is really not neccessary, this is more about a performance concern. Due to the lack of DBA skills and high criticity of this DB. The best choice is to not shrink unless neccessary. What do you think ? – Meoffrey May 22 at 8:26
  • Yeah, leave it as it is. There are no upsides of shrinking (except getting back some disk space), only downsides. My article on the topic, if you want some more beef: karaszi.com/… – Tibor Karaszi May 22 at 9:31

Browse other questions tagged or ask your own question.