Azure SQL : bases d'un pool élastique et espace utilisé
Requêtes T-SQL pour lister les bases par pool élastique et suivre leur taille avec sys.resource_stats.
Requêtes T-SQL pour lister les bases d'un serveur Azure SQL Database avec leur pool élastique et suivre l'espace utilisé.
Bases de données et pool élastique
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
Dernière taille collectée par base
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
Taille des bases d'un pool élastique donné
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'