LLI Wiki — My Digital Knowledge Base

Azure SQL: elastic pool databases and storage used

T-SQL queries listing databases per elastic pool and tracking their size with sys.resource_stats.

T-SQL queries to list the databases of an Azure SQL Database server with their elastic pool and to track the storage they use.

Databases and 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

Last collected size per database

SELECT
    [database_name],
    start_time AS 'LastCollectionTime',
    storage_in_megabytes AS 'CurrentSize(MBs)',
    allocated_storage_in_megabytes AS 'AllocatedStorage(MBs)'
  FROM (
            SELECT
                ROW_NUMBER() OVER(PARTITION BY [database_name] ORDER BY start_time DESC) AS rn,
                [database_name],
                start_time,
                storage_in_megabytes,
                allocated_storage_in_megabytes
            FROM sys.resource_stats
        ) rs
WHERE rn = 1

Database sizes within a given elastic pool

SELECT
    [database_name],
	dso.elastic_pool_name,
    start_time AS 'LastCollectionTime',
    storage_in_megabytes AS 'CurrentSize(MBs)',
    allocated_storage_in_megabytes AS 'AllocatedStorage(MBs)'
  FROM (
            SELECT
                ROW_NUMBER() OVER(PARTITION BY [database_name] ORDER BY start_time DESC) AS rn,
                [database_name],
                start_time,
                storage_in_megabytes,
                allocated_storage_in_megabytes
            FROM sys.resource_stats
        ) rs
	inner join sys.databases d on d.name  =  rs.database_name
	inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE rn = 1 and dso.elastic_pool_name =  'elasticpool01'
Mastodon LLIMastodon SocialFosstodon