The storage limit for SQL Managed Instance General Purpose is getting doubled! You can create or upgrade your GP Instances to host up to 16 TB of data.

What is new?

For many database workloads with significant data volume, the previous limit for SQL MI GP storage size of 8 TB has been a blocker. Microsoft is constantly working on improving and increasing SQL MI service and now the storage limit for GP is doubled - you can create or upgrade your GP Instances to host up to 16 TB of data.

Are there any requirements?

There are 2 requirements for having more than 8TB of storage: the minimum number of vCores and the minimum number of files.

Minimum number of vCores

Previously, maximum storage size for SQL MI GP was 8 TB, which was available for every instance that had at least 8 vCores (Figure 1). Now, with GP 16 TB in preview, this experience changes such that the maximum storage size of 16 TB becomes an option for every instance that has at least 16 vCores (Figure 2). One more thing that is different is a notice that appears as soon as a customer moves the slider to 16 vCores (Figure 3).

Figure 1- Old Portal experience (maximum storage size of 8TB)

Figure 2- New Portal experience (maximum storage size of 16TB for every instance with minimum 16vCores)

Figure 3- New notice on the portal about the preview

Minimum number of files

General Purpose is a SQL MI architecture where compute layer (which runs the SQL Server Database Engine process and is being used for query processing & data caching) is separated from the storage layer where all data & log files (.mdf, ldf, .ndf) are placed. The data layer is implemented using Azure Storage that has built-in replication and redundancy that guarantee that the data will be preserved. For more details about the SQL MI GP architecture take a look at this post. 

Every database file is placed on a separate Azure Storage disk (one file per disk). These disks that are used in storage layer have fixed sizes (Figure 4), and SQL MI uses minimal disk size that is required to fit the database file. It is important to note that at this time, the maximum file size supported by the underlying storage is 8 TB, which means that you will need at least two files to exceed 8 TB of the storage size.

Figure 4 - File sizes

This is not expected to be a permanent requirement as we are looking forward to increasing the maximum file size in the future.

Recommended steps

If you have only one file and the data volume hit the file size limit of 8TB, you will get an error message:

Msg 1105, Level 17, State 2, Line 86\nCould not allocate space for object 'dbo.test' in database 'testfilelimit' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.\n

Therefore, you might want to consider adding a new file once you reach 8TB of an existing file. To help you deal with this, here are a couple of useful scripts you may want to reuse:

1.      To add a new file

You may want to add a new file before hitting the file size limit of 8TB.

Code template:

ALTER DATABASE <database_name>\nADD FILE\n(\n    NAME = <file_name>,\n    SIZE = <file_size>,\n    MAXSIZE = <max_file_size>,\n    FILEGROWTH = <filegrowth>\n)\n

To set more parameters when creating the new file, take a look at how to add a file to a new database with T-SQL.

2.      To monitor the file size(s)

You may want to monitor the used file space to act before hitting the file size limit of 8TB.

Code example:

SELECT DB_NAME() AS database_name,\n    name,\n    type_desc,\n    size/128.0/1024.0 AS reserved_size_gb,\n    CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0/1024.0 AS used_space_gb\nFROM sys.database_files\n

3.      To send  an alert using db_mail

You may want to send yourself an email if the file size is approaching 8TB. You can enable db_mail feature and send the email alerts directly from Managed Instance once you are close to resource limits. If you haven’t configured emails on Managed Instance, take a look at this post.

Here is a code example to send an alert when the file size is approaching 8TB (above 90% used):

declare @used_file_size INT;\ndeclare @instance nvarchar(200) = @@SERVERNAME;\n\nSELECT TOP 1\n    @used_file_size = CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0/1024.0\nFROM sys.database_files\n\nif(@used_file_size > .9*8192)\nbegin\n    declare @msg nvarchar(max) = CONCAT('You are reaching the file size limit of 8TB for instance ', @@SERVERNAME, ':\n    Used file size ', @used_file_size, '.\n    Consider upgrading the instance.');\n    exec msdb.dbo.sp_notify_operator\n     = N'AzureManagedInstance_dbmail_profile',\n    @name = N'DevOps team',\n    @subject = N'Azure SQL Instance - Storage limit alert',\n    @body = @msg;\nend

4.      To schedule a SQL Agent job for alerting

You can easily automate db_mail alerts and copy/paste the previous query into SQL Agent job that will run every 15 minutes and send the email if you are reaching the file size limit of 8TB (Figure 5).

 Figure 5 - New Job step to monitor used file size

Summary

In this article, we discussed the requirements for the increased storage limit to 16 TB for SQL Managed Instance General Purpose and recommended steps to monitor the used file sizes. Thank you for reading and enjoy using SQL Managed Instance with increased storage capacity.

What is new?

For many database workloads with significant data volume, the previous limit for SQL MI GP storage size of 8 TB has been a blocker. Microsoft is constantly working on improving and increasing SQL MI service and now the storage limit for GP is doubled - you can create or upgrade your GP Instances to host up to 16 TB of data.

Are there any requirements?

There are 2 requirements for having more than 8TB of storage: the minimum number of vCores and the minimum number of files.

Minimum number of vCores

Previously, maximum storage size for SQL MI GP was 8 TB, which was available for every instance that had at least 8 vCores (Figure 1). Now, with GP 16 TB in preview, this experience changes such that the maximum storage size of 16 TB becomes an option for every instance that has at least 16 vCores (Figure 2). One more thing that is different is a notice that appears as soon as a customer moves the slider to 16 vCores (Figure 3).

Figure 1- Old Portal experience (maximum storage size of 8TB)

Figure 2- New Portal experience (maximum storage size of 16TB for every instance with minimum 16vCores)

Figure 3- New notice on the portal about the preview

Minimum number of files

General Purpose is a SQL MI architecture where compute layer (which runs the SQL Server Database Engine process and is being used for query processing & data caching) is separated from the storage layer where all data & log files (.mdf, ldf, .ndf) are placed. The data layer is implemented using Azure Storage that has built-in replication and redundancy that guarantee that the data will be preserved. For more details about the SQL MI GP architecture take a look at this post. 

Every database file is placed on a separate Azure Storage disk (one file per disk). These disks that are used in storage layer have fixed sizes (Figure 4), and SQL MI uses minimal disk size that is required to fit the database file. It is important to note that at this time, the maximum file size supported by the underlying storage is 8 TB, which means that you will need at least two files to exceed 8 TB of the storage size.

Figure 4 - File sizes

This is not expected to be a permanent requirement as we are looking forward to increasing the maximum file size in the future.

Recommended steps

If you have only one file and the data volume hit the file size limit of 8TB, you will get an error message:

Therefore, you might want to consider adding a new file once you reach 8TB of an existing file. To help you deal with this, here are a couple of useful scripts you may want to reuse:

1.      To add a new file

You may want to add a new file before hitting the file size limit of 8TB.

Code template:

To set more parameters when creating the new file, take a look at how to add a file to a new database with T-SQL.

2.      To monitor the file size(s)

You may want to monitor the used file space to act before hitting the file size limit of 8TB.

Code example:

3.      To send  an alert using db_mail

You may want to send yourself an email if the file size is approaching 8TB. You can enable db_mail feature and send the email alerts directly from Managed Instance once you are close to resource limits. If you haven’t configured emails on Managed Instance, take a look at this post.

Here is a code example to send an alert when the file size is approaching 8TB (above 90% used):

4.      To schedule a SQL Agent job for alerting

You can easily automate db_mail alerts and copy/paste the previous query into SQL Agent job that will run every 15 minutes and send the email if you are reaching the file size limit of 8TB (Figure 5).

 Figure 5 - New Job step to monitor used file size

Summary

In this article, we discussed the requirements for the increased storage limit to 16 TB for SQL Managed Instance General Purpose and recommended steps to monitor the used file sizes. Thank you for reading and enjoy using SQL Managed Instance with increased storage capacity.

The storage limit for SQL Managed Instance General Purpose is getting doubled! You can create or upgrade your GP Instances to host up to 16 TB of data.

I am also inline with what yowzermiller has said. We are also keen to increase our storage limit around 50 TB in 1-2 years and Azure SQL MI would have been a good fit if Microsoft has plans to increase the storage capacity to at least 64 TB within couple of years.

I really prefer Managed Instance over Azure SQL DB primarily because during migration companies tend to have a lot of cross database joins, which actually are not bad, but are not supported in Azure SQL DB.  The main limitation of MI is the space limit.  Do they plan on increasing MI to more like 64 TB or even 100 TB?  16 TB is nothing these days.

Hello Nevena Nikolic, All other's,

Can you help me in understanding by when this increased limit will be live , I mean it's still in preview mode so when it can be expected to be live at production for users. Accordingly we can plan other integrations for SQL MI up to 16 TB .

Does the maximum number of data files also change with this enhancement?  The current documented limit is 280.

Also, what about the maximum number of databases which is currently 100?

Resource limits - Azure SQL Managed Instance | Microsoft Docs

Awesome news! Is there any ETA for this 16 TB storage limit for SQL MI GP preview feature going into GA/production? Some preview features have been recommended by engineering team to be used in production (as an exception). Is this one of them?