TSQL to find database count in each Elastic pool and its storage utilization – Azure

-- TSQL to find database count in each Elastic pool and its storage utilization.
-- This needs to be executed against SQL Serve on Master Database 
; WITH CTE1 AS (
select
       isnull(d2.elastic_pool_name,'Not in Pool') as PoolName, count(*) as DatabaseCount 
from
       sys.databases d1 inner join sys.database_service_objectives d2 on d1.database_id = d2.database_id
	   where d1.name <> 'master'
group by isnull(d2.elastic_pool_name,'Not in Pool') )
, CTE2 as (
select elastic_pool_name, avg_storage_percent, elastic_pool_storage_limit_mb
,ROW_NUMBER() OVER (PARTITION BY elastic_pool_name order by end_time desc) as myrow
from sys.elastic_pool_resource_stats WHERE end_time > dateadd(hh,-1,getdate()) ) 
select @@Servername as ServerName, CTE1.PoolName, CTE1.DatabaseCount, CTE2.avg_storage_percent, CTE2.elastic_pool_storage_limit_mb	 from CTE1 join CTE2 on CTE1.PoolName = CTE2.elastic_pool_name
where cte2.myrow = 1 
-- 

Advertisements
This entry was posted in Azure, Script, SQL, SQL Server, Uncategorized and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s