Quickly Determining Azure SQL Database Size

On By John Morehouse

Last Updated on July 2, 2021 by John Morehouse

Recently I needed to determine how much storage space each database on a logical server was consuming.  I was doing some DR testing and I need to give the client a rough estimate on how much each database was going to cost.  You can do this through the Azure portal where you interrogate each individual database, however, this is a long and tedious process especially if you have many databases on a given instance.

Essentially, I was looking for the information shown below in the red box.

I did not want to have to go into each database and manually record the appropriate values.  Instead, I went looking for a faster way and discovered sys.resource_stats.

What is sys.resource_stats?

This is a dynamic management view (DMV) that is explicitly available to Azure SQL Database and Azure SQL Managed Instance.  In other words, you will not be able to find this DMV available with on-premises installations including those instances installed on an Azure Virtual Machine.

This DMV will show us information related to the amount of storage that a database is consuming as well as how much storage is allocated to the database currently.  This DMV will not, however, show you the maximize amount of storage the database can consume.

Here is a quick script that you can use to easily determine how much consumed and allocated storage each database has.

SELECT MAX(start_time) 'Last Collection Time'
    , database_name, MAX(storage_in_megabytes) 'Current Size(MBs)'
    , MAX(allocated_storage_in_megabytes) 'Allocated Storage (MBs)'
FROM sys.resource_stats 
GROUP BY database_name

We can see that by using this DMV the values returned match the values provided via the portal.

One thing to note is that if the database is in the serverless tier and it has not been online for some time, this DMV will not capture the information. However, if you the database has been active, the data will be available to you.

Summary

This was a quick script to determine quickly and easily how much storage my clients’ databases are consuming.  Armed with this information it made it easier to calculate the pricing information they were asking for without having to interrogate the Azure portal.

© 2021, John Morehouse. All rights reserved.

One Reply to “Quickly Determining Azure SQL Database Size”

  1. A couple of comments regarding your excellent article:
    1. Despite the documentation for sys.resource_stats, this view does not appear to exist in Azure Managed Instance. There is however a sys.resource_stats_raw (currently undocumented) which appears to be equivalent.

    MAX(storage_in_megabytes) is not necessarily the same as getting the current file space used, it could have got smaller. I believe your query should be based on the data from the “Last Collection Time” for each database, as in :
    WITH resource_stats_cte
    AS (SELECT database_name [Database Name],
    start_time [Last Collection Time],
    storage_in_megabytes [Current Size(MBs)],
    allocated_storage_in_megabytes [Allocated Storage (MBs)],
    ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY start_time DESC) RankRow
    FROM sys.resource_stats)
    SELECT [Database Name],
    [Last Collection Time],
    [Current Size(MBs)],
    [Allocated Storage (MBs)]
    FROM resource_stats_cte
    WHERE RankRow = 1
    ORDER BY
    [Database Name];

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.