Monitor local storage usage on General Purpose Azure SQL Managed Instance

Managed Instance has predefined space that depends on the values of reserved and vCores that you choose when you provision the instance. In this post you will see monitor space on the Managed Instance.

In Managed Instance you can reach three storage limits:

  • Storage limit of managed instance that you choose on the portal. This limit cannot be bigger than 8TB in General Purpose or 4TB in Business Critical. In this post you can find check storage usage and create alerts using SQL Agent.
  • (General Purpose only) Allocation limit of underlying remote Azure premium storage which is described here.

If you reach the storage limit, you would need to increase the storage space/number of vCores or to free some resources. It is important to add more storage before you reach the limits, because changing storage is done using upgrade service tier operation that can take few hours.

sys.dm_os_volume_stats provide information about the volumes including total and used storage on the Managed Instance. You can find storage information using the following query:

SELECT volume_mount_point,
used_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),
available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),
total_gb = CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(8,1))
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY volume_mount_point;

If you execute this query, it will return you amount of total, available and used storage on remote azure premium storage and local SSD:

In this case, I have 8-core instance that has 8*24GB = 192GB of local SSD storage shown as c:WFRoot volume. http:// volume shows how much storage you ar eusing on remote Azure Premium Disk storage.

You should periodically monitor results of this query and react if you see that available_gb is decreasing, because you might get out of the space.

You can also create SQL Agent Job that will periodically run this query and send you a warning using db_mail if you will reach the maximum storage space. In this post you can find check remote storage usage and create alerts using SQL Agent so you can use the similar approach with the local storage.

 

This article was originally published by Microsoft's Entra (Azure AD) Blog. You can find the original article here.