Metric Storage space used

Question

I see this value in the attached screenshot and would like to clarify its meaning.

Additionally, I need to determine how much free storage space is available in my SQL Managed Instance. Ideally, I should see a clear breakdown, such as "10 GB used" or "100 GB used."

Below are the sizes of my databases:

• 24.00 MB
• 40.00 MB
• 40.00 MB
• 49.50 MB
• 24.00 MB
• 65.50 MB
• 25.06 MB
• 25.06 MB
• 208.00 MB
• 101048.00 MB

The total database size exceeds 10 GB, so I need to understand how the reported 101.39 K (Avg) value aligns with my actual storage usage.

Can you help clarify this?

Thanks

Answer 1

Hi @ GP

Thanks for the Question and using Microsoft Q&A

The value "101.39 K (Avg)" you see in the screenshot likely refers to the average amount of storage space used by your databases, measured in kilobytes (KB). This average is calculated based on the total storage space used by all your databases.

To get a clearer picture of your storage usage, you can use the following SQL query to check the allocated and used space for each database file:

-- Connect to a user database
SELECT 
    file_id, 
    type_desc, 
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024 AS space_used_mb, 
    CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb, 
    CAST(size AS decimal(19,4)) * 8 / 1024 AS space_allocated_mb, 
    CAST(max_size AS decimal(19,4)) * 8 / 1024 AS max_size_mb 
FROM 
    sys.database_files;

This query will give you a breakdown of the space used, unused, and allocated for each database file in megabytes (MB).

To determine the free storage space available in your SQL Managed Instance, you can use the following query:

-- Get the total reserved and used storage space
SELECT 
    reserved_storage_mb, 
    storage_space_used_mb, 
    CAST(storage_space_used_mb * 100. / reserved_storage_mb AS DECIMAL(9,2)) AS ReservedStoragePercentage 
FROM 
    master.sys.server_resource_stats 
ORDER BY 
    end_time DESC;

This query will show you the total reserved storage space, the used storage space, and the percentage of reserved space that is currently used.

Please go through these Documentations that might help you:

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

https://techcommunity.microsoft.com/blog/azuresqlblog/how-to-find-out-reserved-and-available-disk-space-on-sql-mi/2636930

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.