1

In Azure I have SQL Database elastic pool on a DTU based purchasing model (Standard). In the Pool I have 7 SQL databases

These are my storage stats:

  • Used Space 50GB
  • Allocated Space 105GB
  • Maximum Storage size 150GB

We had some rogue processes going wild on IO logging that caused the big amount of allocated space and now I want to reclaim it.

I'm thinking of shrinking the databases and then rebuilding the indexes. This is very time consuming and maybe this is not the right way to go for a SQL Database elastic pool.

So my question is... Is this the way to solve this issue or are there alternatives to reclaim the allocated space?

A follow up question would then be is there some kind of automated solution possible to keep this allocated space closer to the used space?

Thank you for your time.

2
  • 1
    Just keep in mind rebuilding your indexes will likely cause your space to regrow, perhaps not to the amount currently allocated, but generally not trivially either.
    – J.D.
    Mar 3, 2022 at 16:38
  • @J.D. thank you. Yes I'm also curious how much space the rebuild will take. If needed reorganize is also an option but to be honest I don't mind it growing a bit more. My main concern is used and allocated are to far apart and as soon as allocated reaches max you need to grow your pool data size again. Mar 4, 2022 at 13:16

1 Answer 1

1

This weekend I did the above maintenance on the elastic pool. So I first shrinked all databases and after do a full rebuild on the indexes. The result is just fine:

  • Used space 9GB
  • Allocated space 19.5GB
  • Max size 150GB Happy with the result.

Your Answer

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

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