In an attempt to identify anomalies cause by modifications to my Cloud Services, I decided to monitor the size of the tables I created in Windows Azure SQL Database. And since I have a rough estimate of the number of records that each table is supposed to contain, I can raise alerts when they start growing out of control.
The following query produces a list of non-system tables along with their size in gigabytes and the number of records they contain.
SELECT
o.name AS [table_name],
sum(p.reserved_page_count) * 8.0 / 1024 / 1024 AS [size_in_gb],
p.row_count AS [records]
FROM
sys.dm_db_partition_stats AS p,
sys.objects AS o
WHERE
p.object_id = o.object_id
AND o.is_ms_shipped = 0
GROUP BY o.name , p.row_count
ORDER BY o.name DESC
o.name AS [table_name],
sum(p.reserved_page_count) * 8.0 / 1024 / 1024 AS [size_in_gb],
p.row_count AS [records]
FROM
sys.dm_db_partition_stats AS p,
sys.objects AS o
WHERE
p.object_id = o.object_id
AND o.is_ms_shipped = 0
GROUP BY o.name , p.row_count
ORDER BY o.name DESC
Alexandre Brisebois ☁ 