How to: Calculate SQL Azure DB/Database Size (sp_spaceused unavailable)

The good old sp_spaceused is unavailable in SQL Azure. But fortunately we have the underlying data view available. Its called sys.dm_db_partition_stats.

Here's a simple query to get the total "reserved space" of a database.

SELECT SUM(reserved_page_count) * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats

You might also want to get the size of each table seperately. You just have to join it with the sys.objects table and remove the summation.

SELECT Name, reserved_page_count * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats PS
JOIN sys.objects SO ON PS.object_id=SO.object_id

Hope this helps!

0 comments:

Post a Comment

Please let me know if you find it useful!