Is there a way to get the allocated storage data percentage metric from Azure SQL database resource for creating an alert?

Question

I'd like to create an alert that will monitor the allocated data storage for an SQL database in Azure, so that I know when it is about to reach its allocated data storage capacity. Ideally, something like storage_percent would be perfect since it monitors percentage and not in bytes. But I want to track the allocated data storage.

Here is a list of metrics that can be monitored by an alert: https://learn.microsoft.com/en-us/azure/azure-monitor/platform/metrics-supported#microsoftsqlserversdatabases

There is no any metric that can track percentage, only bytes. (allocated_data_storage has Bytes units)

My workaround at the moment is to retrieve the allocated data storage in bytes and then multiply that value by the threshold I'd like to be alerted about.

e.g.
threshold to trigger alert is 75%
allocated_data_storage is 4 GB
alert me when database storage is greater than 4 GB * 0.75 = 3 GB

But this doesn't seem reliable since a database is prone to be scaled up/down in data size. So, if the allocated data storage gets increased to 10 GB, my alert will still be monitoring for data storage under 3 GB, which is now under 75% of allocated data storage.

Answer 1

We could get the used space/ allocated space/Maximum storage size on Portal:

Or You use bellow query in database:

-- Connect to database
-- Get database data space allocated in MB, max database stroage in MB and database data space allocated used in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, 
SUM(max_size/128.0) AS DatabaseDataSpaceMaxInMB,
SUM (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceUsedInMB 
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS'

You could create a new query with the values to get the alert value to built the alert rule, such as percent of AllocatedSpace/UsedSpace.

Since Azure SQL database doesn't support the send email feature, we could use Logic app to trigger it:

1. Create a Recurrence trigger: schedule run the trigger.
2. Add an Execute a SQL Query action: to get the alert value.
3. Add Condition: to judge the if the alert value is greater than 75, if true, send the email!

Logic app example overview: