Monitor local storage usage on General Purpose Azure SQL Managed Instance

Azure SQL Managed Instance has predefined storage space that depends on the values of reserved storage and vCores that you choose when you provision the instance. In this post you will see how to monitor storage 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 how to check storage usage and create alerts using SQL Agent.
• (General Purpose only) Allocation limit of underlying remote Azure premium storage which is described here.

• (General Purpose only) the storage limit of local SSD disk storage - in Managed Instance tempdb is placed in local SSD that can have 24GB*<number of vCores> space. If you reach this limit, you would not be able to create temporary objects in tempdb.

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. https:// 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 how to check remote storage usage and create alerts using SQL Agent so you can use the similar approach with the local storage.

• Anonymous
  March 08, 2019
  Thanks, Helpful post :-)