Monday, October 21, 2013

Windows Azure Database dimensions in Mb

If you use a Windows Azure database, you know that you can't easily know its dimension (in term of used space) by the Sql Management Studio, you have to use the Azure Portal. But the portal is not update in real time... So, if you wanna know the database used space, you can use this query:

/*Database dimension in MB*/
SELECT SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats


Instead, if you wanna know the dimension in MB for each table in the Database, use the query below:

/* Tables Dimensions in MB */
SELECT sys.objects.name AS 'Table Name', SUM(reserved_page_count) * 8.0 / 1024 AS 'Mb'
FROM sys.dm_db_partition_stats
INNER JOIN sys.objects ON sys.dm_db_partition_stats.object_id = sys.objects.object_id
WHERE sys.objects.type = 'U'
GROUP BY sys.objects.name