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://docs.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.

allocated_data_storage is different from Maximum storage size, the maximum provisioned size of your database. Do you want to get alerted when the data space used is > 75% of your Max storage size? — Kalyan Chanumolu-MSFT
@KalyanChanumolu-MSFT No, I'd like to be alerted when the data space used is > 75% of the allocated storage in a database. Being alerted from max space is no good because there is the case that allocated storage is set to 70% of max space, thus it will throw an error before the alert is triggered. — ornelasfdev
Hi @ornelasfdev, I have the workaround may be helpful for you. I post it as the answer. If it is helpful for you, hope you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2020-09-08T01:14:35

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:

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

Logic app example overview:

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

1 Answers