LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon