Huge storage increase when executing alter table command in azure managed instance database

Question

We are currently using an Azure managed instance to fulfill our data storage requirements. This instance has a database named ISMCRMMirror, housing approximately 24 million records. A recurring aspect of our operations involves the biweekly addition of a new columns to this database. During the execution of this process last week, on the 1st of February, we observed a huge increase in storage capacity within the database while executing an alter query. Following is the query: ALTER TABLE dbo.account SET (SYSTEM_VERSIONING = OFF); GO ALTER TABLE dbo.account ADD [new_cancellationowner] uniqueidentifier NULL, [new_partnermanagername] nvarchar(4000) NULL, [new_partnermanageryominame] nvarchar(4000) NULL; GO ALTER TABLE dbo.accountHistory ADD [new_cancellationowner] uniqueidentifier NULL, [new_partnermanagername] nvarchar(4000) NULL, [new_partnermanageryominame] nvarchar(4000) NULL; GO ALTER TABLE dbo.account SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.accountHistory)   );   The above query also failed due to insufficient storage capacity as a result of the sudden increase in storage during the above process.   ·        The following snapshot shows the state of the database when the query fails.   After this incident happened, we increased to database size by around 50GB and retried the query. But the same incident happened. The extra allocated size was filled within short amount of period during query execution and again the query failed due to lack of memory. The following snapshot shows the current (5^th Feb) state of the database. As you can see space_unused_mb has increased from 53mb to 177866mb (around 177GB). Therefore, we can assume that the database has re-released its storage. But it is not reflecting from the managed instance. Refer below table. We may see a sudden increase as we mentioned above but no decrease. What is the reason of this rapid increase of storage in a short amount of period when running a Alter table command and How we can fix this issue? P.S.: Up to now we have tried the rebuild of both clustered and non-clustered indexes of 4 largest tables of the database. And we are seeing to different statistics on two different places of the Azure portal for used space of managed instance.   Refer below two visualizations.

Above two visualizations showing two different values for the used database storage. Is there any specific reason for this?

Answer 1

Hi
Lalantha Perera •,

Welcome to Microsoft Q&A forum and thanks for such detailed question.

As I understand, you are getting huge increase in Storage of Azure SQL Managed Instance.

We are sorry about the inconvenience you are facing.

If not already done, I would suggest you to follow the steps mentioned here for initial level of troubleshooting:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/file-space-manage?view=azuresql-mi to manage files in DB.

OR

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/identify-query-performance-issues?view=azuresql-mi to identify query performance bottlwnecks.

OR

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/performance-guidance?view=azuresql-mi#best-practices-for-very-large-database-architectures-in-azure-sql-managed-instance consider Best practices for very large database architectures in Azure SQL Managed Instance.

OR

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-with-dmvs?view=azuresql-mi#identify-high-memory-consuming-statements to identify high memory-consuming statements.

As mentioned by you, there are 24 million records and biweekly alter operation is performed in the DB, this scenario requires deeper investigation of your instance and other details.

Hence, I would recommend you to raise support case and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks