question

KishoreA-7788 avatar image
0 Votes"
KishoreA-7788 asked OuryBa-MSFT commented

What happens when Azure SQL Managed Instance reaches 100% storage?

I would like to understand - what happens to Azure SQL Managed Instance when it reached 100% of storage limit ?

Question: Are there any automated process that will run on SQL MI to clean up some space ?

Currently we are using General Purpose Gen5 (8192 GB, 32 vCores)
time storage usage %
2021-04-11 22:00:00.000 95.831251144409179688
2021-04-11 21:00:00.000 95.831251144409179688
2021-04-11 20:00:00.000 100.000011920928955078
2021-04-11 19:00:00.000 100.000011920928955078
2021-04-11 18:00:00.000 100.000011920928955078

No clean up activity was done to make some space available. When I check the per-hour storage usage on our instance, I can see 5% of space got freed up automatically. Can someone help me understand how this happened?

Thanks,
Kishore

azure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

anonymous userA-7788 Welcome to Microsoft Q&A and thank you for posting your question. When Azure SQL managed Instance reaches 100% storage. You get an error, Microsoft does not scale anything. We don't have any feature which can increase the storage space without customer's input. It can change due to index update, some stored procedures cleaning up data.

0 Votes 0 ·

@OuryBa-MSFT

Thanks for your prompt response.
Can you please confirm that the index update happens automatically ?

MI reaches 100% storage --> Index update/rebuild process triggers ( some stored procedures ) --> is this a standard process?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

The space reduction could be due to index rebuilds which can shave off some megabytes, if an index is severely fragmented.

But Azure MI is not going to guess which tables in your table database that are good fits for purging operations. If you think that cleaning out old data is the way to go, you need to do that yourself.

If you reach 100% and there is no room for autogrow, you will face an outage obviously, so, yes, it may be clean-up time.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog
Thanks for your reply.
So when Azure MI reaches 100%, Does INDEX rebuilds happens automatically to free up space ?

In our case, no one did anything and still we got ~5% of free space. I am trying to understand the standard behavior of MI and internal processes

0 Votes 0 ·

So when Azure MI reaches 100%, Does INDEX rebuilds happens automatically to free up space ?

No.

Index rebuilds happens because someone has scheduled them. I don't know for sure, but I don't think Microsoft schedule index maintenance in Azure MI, but you need to do that yourself.

In any case, if it was was an index rebuild that regained some space for you, it was a pure coincidence that it ran at this point.

0 Votes 0 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT commented

Hello anonymous userA-7788 There is no automated process to clean up storage space. What might have happened is log truncation that is triggered after each backup log and gets executed depending on the log state. Thanks


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello anonymous userA-7788 If you want to know more in details what have happened please send an email to azcommunity@microsoft.com and provide the instance name and period to be investigated and we may tell you what happened. Thanks

0 Votes 0 ·