LLI Wiki — My Digital Knowledge Base

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'
Mastodon LLIMastodon SocialFosstodon