Quickly Determining Azure SQL Database Size

Published On: 2021-07-02By:

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.

The post Quickly Determining Azure SQL Database Size first appeared on John Morehouse. Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Award 2021 Gold    American Business Award 2021 Bronze    FT Americas’ Fastest Growing Companies 2021   INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award
FT Americas’ Fastest Growing Companies 2020       Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award
Share via
Copy link