Azure SQL: database and elastic pool size queries (T-SQL)
T-SQL queries to list databases per elastic pool and measure used, allocated and free storage.
Queries to list the databases of an Azure SQL Database logical server per elastic pool and to measure database and pool storage. Run them in the master database of the logical server, where sys.resource_stats is available.
Databases per elastic pool
SELECT
@@SERVERNAME as [ServerName],
dso.elastic_pool_name,
d.name as DatabaseName,
dso.edition
FROM
sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
d.name, dso.elastic_pool_name
Pool of a single database:
SELECT
@@SERVERNAME as [ServerName],
dso.elastic_pool_name,
d.name as DatabaseName
FROM
sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master' and d.name = 'AppDb'
ORDER BY
dso.elastic_pool_name
Current statistics of an elastic pool
SELECT
elastic_pool_name,
*
FROM
sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'
Data space used by a pool, in MB (latest sample):
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 = 'pool01'
ORDER BY end_time DESC
Same with the allocated storage percentage:
SELECT TOP 1 avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'
ORDER BY end_time DESC
Latest size of every pool:
WITH RankedElasticPoolStats AS (
SELECT
elastic_pool_name,
avg_allocated_storage_percent,
elastic_pool_storage_limit_mb,
ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY end_time DESC) AS rn
FROM
sys.elastic_pool_resource_stats
)
SELECT
elastic_pool_name,
avg_allocated_storage_percent,
elastic_pool_storage_limit_mb,
avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM
RankedElasticPoolStats
WHERE
rn = 1
ORDER BY
elastic_pool_name;
Database size
History of one database:
USE master;
GO
SELECT
database_name,
storage_in_megabytes AS DatabaseDataSpaceUsedInMB, *
FROM sys.resource_stats
WHERE database_name = 'AppDb'
ORDER BY end_time DESC
Latest used size of all databases:
with RankedDatabaseSize as (
SELECT
database_name,
storage_in_megabytes AS DatabaseDataSpaceUsedInMB,
ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
FROM sys.resource_stats
)
select *
FROM RankedDatabaseSize
where rn = 1
order by DatabaseDataSpaceUsedInMB desc
Latest allocated size of all databases:
with RankedDatabaseSize as (
SELECT
database_name,
allocated_storage_in_megabytes AS DatabaseDataSpaceAllocatedInMB,
ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
FROM sys.resource_stats
)
select *
FROM RankedDatabaseSize
where rn = 1
order by DatabaseDataSpaceAllocatedInMB desc
Combined view: pool free space versus the largest databases
For each pool, this query sums the allocated size of its two largest databases (in GB, min_need_space) and shows the free space left in the pool (in GB). It helps check whether a pool has enough room, for example before moving or growing databases.
with linkdbep as
(
SELECT
@@SERVERNAME as [ServerName],
dso.elastic_pool_name,
d.name as DatabaseName
FROM
sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
),
RankedDatabaseSize as (
SELECT
database_name,
allocated_storage_in_megabytes AS DatabaseDataSpaceAllocatedInMB,
ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
FROM sys.resource_stats
),
RankedElasticPoolStats AS (
SELECT
elastic_pool_name,
avg_allocated_storage_percent,
elastic_pool_storage_limit_mb,
ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY end_time DESC) AS rn
FROM
sys.elastic_pool_resource_stats
),
linkdata as (
select kde.ServerName
, rds.DatabaseDataSpaceAllocatedInMB
, kde.elastic_pool_name
, DatabaseName, reps.avg_allocated_storage_percent / 100.0 * reps.elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
, elastic_pool_storage_limit_mb
, elastic_pool_storage_limit_mb - ( reps.avg_allocated_storage_percent / 100.0 * reps.elastic_pool_storage_limit_mb) as elastic_pool_storage_free_mb
from linkdbep kde
inner join RankedDatabaseSize rds on kde.DatabaseName = rds.database_name
inner join RankedElasticPoolStats reps on reps.elastic_pool_name = kde.elastic_pool_name
where rds.rn = 1
and reps.rn = 1
), computecte as (
select
ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY DatabaseDataSpaceAllocatedInMB DESC) AS rn,
ServerName,
elastic_pool_name,
DatabaseDataSpaceAllocatedInMB,
elastic_pool_storage_free_mb
from
linkdata
)
select elastic_pool_name, sum(DatabaseDataSpaceAllocatedInMB) / 1024 as min_need_space, max(elastic_pool_storage_free_mb) / 1024 as elastic_pool_storage_free_mb
from computecte
where rn <= 2
group by elastic_pool_name
Notes
sys.resource_statsandsys.elastic_pool_resource_statshold samples taken at regular intervals; the queries useROW_NUMBER()ordered byend_time DESCto keep the latest one per database or pool.pool01andAppDbare example names: replace them with your own.