SSRS with Database Sizes

Question

Hi

I'm trying to create a report that will show the size of the databases, but as we have 32GB available on the Azure Managed instance I would like to have a chart that shows the % we have used, could anyone help with this please.

I'm gathering the database size from the following sql

WITH resource_stats_cte

AS (SELECT database_name [Database Name],

start_time [Last Collection Time],

storage_in_megabytes [Current Size(MBs)],

allocated_storage_in_megabytes [Allocated Storage (MBs)],

ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY start_time DESC) RankRow

FROM sys.resource_stats_raw)

SELECT [Database Name],

[Last Collection Time],

[Current Size(MBs)],

[Allocated Storage (MBs)]

FROM resource_stats_cte

WHERE RankRow = 1

ORDER BY

[Database Name];

Thanks

Clive

Answer 1

@Clive Wightman Thanks for posting your question in the Microsoft Q&A forum.

To create a report that shows the size of the databases and the percentage of space used, you can use Azure Monitor. Azure Monitor provides metrics and logs that you can use to monitor the performance and health of your Azure resources, including Azure SQL Managed Instance.

If you are looking for instance level it's in the portal (max used vs max reserved size - 2 counters you can add) under metrics.  DB level would be a T-SQL query that looks at the file size vs. the space.  There is also a DMV that contains the reserved storage space and used space (instance level) if you want to keep it all in T-SQL - master.sys.server_resource_stats 

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-sql-managed-instance-azure-monitor?view=azuresql

Regards

Geetha