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'