Azure SQL : taille des bases et des pools élastiques (T-SQL)
Requêtes T-SQL pour lister les bases par pool élastique et mesurer le stockage utilisé, alloué et libre.
Requêtes pour lister les bases d'un serveur logique Azure SQL Database par pool élastique et mesurer le stockage des bases et des pools. À exécuter dans la base master du serveur logique, où sys.resource_stats est disponible.
Bases de données par 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
Pool d'une seule base :
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
Statistiques actuelles d'un pool élastique
SELECT
elastic_pool_name,
*
FROM
sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'
Espace de données utilisé par un pool, en Mo (dernier échantillon) :
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
Même calcul avec le pourcentage de stockage alloué :
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
Dernière taille de chaque 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;
Taille des bases
Historique d'une base :
USE master;
GO
SELECT
database_name,
storage_in_megabytes AS DatabaseDataSpaceUsedInMB, *
FROM sys.resource_stats
WHERE database_name = 'AppDb'
ORDER BY end_time DESC
Dernière taille utilisée de toutes les bases :
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
Dernière taille allouée de toutes les bases :
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
Vue combinée : espace libre du pool et plus grosses bases
Pour chaque pool, cette requête additionne la taille allouée de ses deux plus grosses bases (en Go, min_need_space) et affiche l'espace libre restant dans le pool (en Go). Elle permet de vérifier qu'un pool a assez de place, par exemple avant de déplacer ou d'agrandir des bases.
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
Remarques
sys.resource_statsetsys.elastic_pool_resource_statscontiennent des échantillons pris à intervalles réguliers ; les requêtes utilisentROW_NUMBER()trié parend_time DESCpour garder le dernier par base ou par pool.pool01etAppDbsont des noms d'exemple : les remplacer par les vôtres.