How many files you can create in General Purpose Azure SQL Managed Instance?

Azure SQL Database Managed Instance is SQL Server Database Engine hosted in Azure cloud. General Purpose tier of Managed Instance is designed for the most common workloads and should match performance of SQL Server in equivalent Azure VM. Managed Instance in General Purpose tier keeps all database files on Azure storage, while the SQL Server Database Engine is placed on stateless compute. See more information about storage layout in General Purpose tier here.

Managed Instances in General Purpose tier can have up to 280 database files but in some cases this limit can be even lower due to internal fragmentation of underlying Azure storage container where the files are placed.

As a result you might get a storage error when you RESTORE or CREATE new database or even when you load data in existing database (although in some edge cases) even if you haven’t reached instance storage limits, or 280 database file limit.

In this article you will see how to calculate the number of remaining files in Managed Instance.

This is important to know because every Managed Instance has up to 35TB of internal Azure Premium storage. This means that once you provision Managed Instance you have two storage limits:

1. Managed instance user storage is the managed instance storage size that you choose on portal and you pay for this amount of storage
2. Internal physically allocated azure premium disk storage that cannot exceed 35TB

When you create database files, they are allocated on the azure premium disks with the sizes that are greater than file size so Managed Instance has some “internal fragmentation” of files. As an example, if you have 200GB file it would be placed on 256GB disk. This is implemented because Azure Premium Disk storage offers fixed set of disk sizes, so Managed Instance tries to fit database files on the matching disk.

The sum of the allocated disk sizes cannot be greater than 35TB. If you reach the limit, you might start getting the errors even if you don’t reach user-defined Managed Instance storage limit.

The following query calculates allocated storage and remaining files (under assumption that they would have size less than 128GB):

SELECT  allocated_size_tb = size_tb,
remaining_128GB_files = CAST((35 - size_tb) * 8 as int)
FROM
( SELECT
SUM(CASE
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 128
THEN 128
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  > 128 AND  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 256
THEN 256
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  > 256 AND  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 512
THEN 512
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  > 512 AND  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 1024
THEN 1024
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  > 1024 AND  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 2048
THEN 2048
WHEN  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  > 2048 AND  CAST(size * 8. / 1024 / 1024 AS decimal(12,4))  <= 4096
THEN 4096
ELSE 8192
END)/1024.
FROM master.sys.master_files
WHERE physical_name LIKE 'https:%'
) AS alloc(size_tb)

The results of this query look like:

Remaining files in Managed Instance

In most of the cases you would get the same number if you just count remote files and subtract this number from 280:

select 280 -
(select count(*) from sys.master_files WHERE physical_name LIKE 'https:%')

However, in some case these two formulas would not match and the actual limit from the first query would be lower as you can see in the example here.

Note that this query returns number of additional 128GB files that you can add. If you are trying to restore databases with the files bigger than 128GB this limit would be even lower so you would need to convert 128GB to your expected size.

Why is this important? If you reach the limit you would not be able to add new databases (for example using restore statement) although you haven’t reached 280 database file limits, because every new file would require at least 128GB of allocated space. In addition, if you are on the limit and some of your files is trying to increase the size from 126GB to 130GB this operation will fail because Managed Instance cannot allocate more storage on the container.

It is important to periodically monitor these limits and shrink files or move databases to another instance if this number is getting low.