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.