I have a 600GB DB with 400GB free space in the data file on Azure SQL MI. I am planning to shrink it and save the storage, but it's a critical DB with the user sessions.

There are two plans:

Plan A: Shrink the data file down to 210GB at one time.

Plan B: Shrink 5GB every time and execute one by one.

Do you have any thoughts? What's the benefit of plan B?

1 Answer 1


Mainly the only difference I can think of is Plan A will cause continuous database contention, where Plan B will be shorter periods of contention at a time, but could take longer to complete overall. If you have a very busy database, Plan B may minimize performance issues, but if your database is set to grow in increments greater than the rate you can shrink by 5 GB, then it may be a fruitless attempt.

Additionally, you shouldn't normally be shrinking your database as it's generally a wasteful heavier operation. Instead you should determine what caused the large growth, if it was normal, and if your database growth settings are appropriately configured.

  • 1
    As an additional point, doing the shrink in a loop will allow you to "complete" some shrinking, so if something were to happen, you don't have to start all over. With an all-in-one shrink, if the query fails, gets killed, etc. you will have to shrink from the beginning.
    – BCM
    Jul 27, 2021 at 14:33
  • 1
    Thanks, J.D. Since it is a busy database and I will try PLAN B. much appreciated your suggestions. Aug 11, 2021 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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