Azure SQL Maximum Storage Size Increase Automation

Question

Can we increase maximum storage size of Azure SQL database automatically when it is near full? Looking option something like autoscaling of DTU using logic apps or run books etc.

Answer 1

You can run the following SELECT statement to determine the current size of the database.

SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) AS DatabaseSizeInBytes,  
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB,  
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DatabaseSizeInGB  
FROM sys.database_files  
WHERE type_desc = 'ROWS';  

You can compare that againt the current maximum size for the database

 SELECT CAST(DATABASEPROPERTYEX ('YouAzureSQLDB' , 'MaxSizeInBytes' ) as bigint) /1073741824 -- Actual Max Size  

To increase that maximum size you can use below statement.

 ALTER DATABASE DATABASE_NAME  
 MODIFY (EDITION='STANDARD', MAXSIZE=50 GB)  

You can automate this verification using a runbook.

Answer 2

Yes, you can create such "automation", there are at least two options available:

If you already have some kind of monitoring, you can create an alarm as a trigger and then run a runbook or PA flow, here is a tutorial where you can find some tips:
https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/monitor-and-scale-database-powershell?view=azuresql

Or if you know especially when such event might happen, e.g. on ultimo you can run a PowerShell script before that and increase the storage with a commandline
https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabase?view=azps-8.1.0