Blog Post

Azure Database Support Blog

3 MIN READ

How to get Azure SQL database size

Copper Contributor

Mar 14, 2019

First published on MSDN on Feb 08, 2019
There are multiple ways to achieve this and there are also some storage types you should be aware

There is a good doc about this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management

Understanding the following storage space quantities are important for managing the file space of a database.

Data space used
- The amount of space used to store database data in 8 KB pages. Generally, space used increases (decreases) on inserts (deletes). In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. For example, deleting one row from every data page does not necessarily decrease the space used.

Data space allocated
- The amount of formatted file space made available for storing database data. The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted.

Data space allocated but unused
- The difference between the amount of data space allocated and data space used. This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.

Data max size
- The maximum amount of space that can be used for storing database data. The amount of data space allocated cannot grow beyond the data max size.

There are multiple methods to query this data

In the Portal is the easiest way to get this for a single database in the overview blade

Or in the database Metrics blade you can also check the historical information , listed as Total Database Size , at this moment represents Used Space .

Its NOT provided in this chart the allocated space

You can also query master or directly the database using TSQL
-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC
OR
-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS'
OR
-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes

For an elastic pool
-- Connect to master
-- Elastic pool data space used in MB
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC
OR
-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC
OR
Connect-AzureRmAccount
# Resource group name
$resourceGroupName = "rg1"
# Server name
$serverName = "ls2"
# Elastic pool name
$poolName = "ep1"
# User name for server
$userName = "name"
# Password for server
$password = "password"

# Get list of databases in elastic pool
$databasesInPool = Get-AzureRmSqlElasticPoolDatabase `
-ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-ElasticPoolName $poolName
$databaseStorageMetrics = @()

# For each database in the elastic pool,
# get its space allocated in MB and space allocated unused in MB.

foreach ($database in $databasesInPool)
{
$sqlCommand = "SELECT DB_NAME() as DatabaseName, `
SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
FROM sys.database_files `
GROUP BY type_desc `
HAVING type_desc = 'ROWS'"
$serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
$databaseStorageMetrics = $databaseStorageMetrics +
(Invoke-Sqlcmd -ServerInstance $serverFqdn `
-Database $database.DatabaseName `
-Username $userName `
-Password $password `
-Query $sqlCommand)
}
# Display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort `
-Property DatabaseDataSpaceAllocatedUnusedInMB `
-Descending | Format-Table

If you want to collect the database size without connecting directly to SQL Server you can query Azure Metrics , as said above ( Total Database Size , at this moment represents Used Space )
Connect-AzureRmAccount

function Get-TotalDatabaseSizeKb
{
param($Resource)
$Result = $Resource | Get-AzureRmMetric -MetricName 'storage' -WarningAction SilentlyContinue
$DBSize = $Result.Data[$Result.Data.Count-2].Maximum
$DBSize / 1024
}

###########################################################################################################
#Single DB
$DB = Get-AzureRmResource -ResourceGroupName "GROUPNAME" -Name "SERVER/DATABASENAME"
$DBSize = Get-TotalDatabaseSizeKb $DB
"DB ($($DB.Name)) $($DBSize)Kb or $($DBSize / 1024)Mb"

###########################################################################################################
#All DB
$Databases = Get-AzureRmResource -ResourceGroupName "GROUPNAME" -ResourceType Microsoft.Sql/servers/databases
foreach ($DB in $Databases)
{
$DBSize = Get-TotalDatabaseSizeKb $DB
"DB ($($DB.Name)) $($DBSize)Kb or $($DBSize / 1024)Mb"
}

Updated Mar 14, 2019

Version 2.0

Azure-DB-Support-Team

Copper Contributor

Joined March 14, 2019

View Profile

Azure Database Support Blog

Follow this blog board to get notified when there's new activity

10 Comments

m60freeman
Brass Contributor
Sep 27, 2024
JSBSF You are definitely solving a different problem than I am trying to. 🙂 I am glad you found a solution that works.

You can do what you need by comparing used vs. allocated, whereas I need to compare allocated vs MAXSIZE and they just don't make the latter available via TSQL for user databases from master.

We can get it using a PowerShell script to iterate through the databases in a pool and produce an aggregated result, but not from TSQL. We only need to get that data on a daily basis for now, so the 15 minute latency on the metrics are not a problem for us.

>They go cheap on us for testing and QA

True for almost everyone. 🙂 We scale the pool up during active testing and back down again when the testing is completed. But as data has grown over time, the lowest tier and number of vCores we can scale to has risen. I wish we had the ability to start and stop the pool instead as that would save us more money. We don't want to test using Serverless, because we don't want to use that for production, and we want to keep things the same between test and production.

>I should also mention that the admin gave us more space on that elastic pool just to immediately buy me some time to sort things out

That's the part I was trying to turn into metrics: knowing when I need to increase MAXSIZE for a database or a pool, based on the current allocated vs. MAXSIZE, and when I need to scale up (and how far) because the MAXSIZE has hit the limit for the number of vCores. Our Infrastructure Team developed some very complex PowerShell to do all that, but it was a lot of effort (including dynamically fetching the current storage limit for each quantity of vCores for each tier, because once we had an initial version working, Microsoft increased many of those limits). The security was also complicated to work with, as each pool uses different access credentials.
JSBSF
Copper Contributor
Sep 27, 2024
Also I should mention that if you are comparing to the stats displayed on Azure portal, if you shrinkdatabase Azure will reflect the change pretty quickly. This query will take a few minutes to update the sizes. It's as if there is some polling or something going on, and while I was shrinking databases from the top down, it would seem to match what I saw on Azure after about 15 or so minutes. In my case I should also mention that the admin gave us more space on that elastic pool just to immediately buy me some time to sort things out, and now I have more than we need.
JSBSF
Copper Contributor
Sep 27, 2024
I just checked. I had modified the query to look like this instead, and this is the one I was eventually using:

SELECT rs2.database_name
, rs2.storage_in_megabytes
, rs2.allocated_storage_in_megabytes
, allocated_storage_in_megabytes - storage_in_megabytes difference
FROM sys.resource_stats rs2
JOIN (
SELECT rs1.database_name
, max(rs1.end_time) as end_time
FROM sys.resource_stats rs1
GROUP BY rs1.database_name) t
ON t.database_name = rs2.database_name
AND t.end_time = rs2.end_time
ORDER BY allocated_storage_in_megabytes - storage_in_megabytes desc, database_name

The reason is that the one I shared just showed storage_in_megabytes for each database. I forgot that I made a change to it. This one shows storage in megabytes, allocated storage in megabytes, and the difference. So I sorted by the difference so that I could see which ones were allocated (a lot) more than was being used. I went through those at the top and ran the shrinkdatabase on them. I was able to do it without concern since the elastic pool in my case was dedicated to testing and QA. I didn't run that on the production server / databases though. They go cheap on us for testing and QA, and we have to drop and shrink all the time to reclaim space.

So, yes, the storage_in_megabytes is the amount consumed, as in data (and maybe log files size?). The allocated_storage_in_megabytes is larger since it accounts for transactions and growth. In my specific case I created a database and started copying data from a production database onto my test database, and got an error since the allocated_storage_in_megabytes pushed it over the top. The whole thing failed, and the database itself resorted to a very small "storage_in_megabytes", however since the copying was adding up "allocated_storage_in_megabytes" the elastic pool had already maxed out.

Now, when the QA guy created a new database as part of his testing, he was unable to. I ran this query and could see that the storage_in_megabytes for the database I added was minimal, but that the allocated_storage_in_megabytes was huge. I shrank that database to solve the problem.
m60freeman
Brass Contributor
Sep 27, 2024
JSBSF I am glad that worked out for you. So you didn't care about MAXSIZE, you just went after the databases with the largest amount of allocated space regardless of whether they were near their individual MAXSIZE or not? I could see where that is a reasonable approach for your use case.

We don't use SHRINK because of the massive overhead of rebuilding the indexes (TB-size databases, hundreds of tables) to fix the fragmentation it causes, and because we would likely need to allocate that space again too quickly, so we just make more space available.

Are you saying that sys.resource_stats.storage_in_megabytes is allocated storage? The documentation isn't clear to me.

I still need MAXSIZE because I am trying to look ahead to ensure I always have about 20% of free space.
JSBSF
Copper Contributor
Sep 27, 2024
I used that the other day because the azure elastic pool had exceeded the amount of allocated space when I tried to copy data into a new database. The databases consume a certain amount which is less than their allocated size, and I wanted to know which ones were allocated the most so I could shrink database on those first. Eventually I was able to free up enough allocated space that I could copy into the new database.
m60freeman
Brass Contributor
Sep 27, 2024
JSBSF That provides the "Maximum storage size in megabytes for the time period, including database data, indexes, stored procedures, and metadata.". But is that the maximum in use, the maximum allocated, or the maximum allowed (storage_limit_mb?) during the period (like the "Data max size (GB)" visible in the database settings in the Azure Portal)?

I want to see, for each database, which databases are getting close to "hitting the wall" on storage so I can decide when to raise that limit or scale up if the max size cannot be increased for the current scale. And I want to get it for all user databases from master.
JSBSF
Copper Contributor
Sep 06, 2024
Can you run something like this on master:

SELECT rs2.database_name
, rs2.storage_in_megabytes
FROM sys.resource_stats rs2
JOIN (
SELECT rs1.database_name
, max(rs1.end_time) as end_time
FROM sys.resource_stats rs1
GROUP BY rs1.database_name) t
ON t.database_name = rs2.database_name
AND t.end_time = rs2.end_time
ORDER BY storage_in_megabytes desc, database_name
m60freeman
Brass Contributor
Feb 22, 2024
karlarr If you run that in master, you get the max_size for master. I don't think there is a TSQL query you can run in master that gets you the size data for each user database. You have to connect to each user database in the Azure SQL Database Server to get the information individually.

Similarly, although you can run this in master to get the max_size for an elastic pool:

SELECT TOP 1 elastic_pool_name, elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
ORDER BY end_time DESC

I don't think there is a TSQL query you can run in master to get the max_size for each user database in the elastic pool.
karlarr
Copper Contributor
Feb 21, 2024
Its possible to get Max Size:

SELECT
[Source] = 'database_files'
,[Data_max_size_GB] = SUM(max_size) * 8 / 1024.0/1024
,[log_max_size_GB] = SUM(max_size)*.3 * 8 / 1024.0/1024
FROM sys.database_files
WHERE type = 0 --ROWS
m60freeman
Brass Contributor
Dec 11, 2019
It is a significant missing feature that there is no way to obtain the current MAXSIZE for a user database via TSQL from within master.