Autoscaling up the tier when a database is running out of space.

Date created: January 5, 2018.

Let's say you may to automate autoscaling the tier of your SQL Azure Database if the size of it is reaching the tier limit. Let's see how you can use Transact-SQL to determine when your database is running out of space and inmediately scale up the tier of your Azure SQL Database.

Azure SQL Databases have a size limit based on the tier assigned to them. You can use DATABASEPROPERTYEX to know the storage limit of an Azure SQL Database.

SELECT Edition = DATABASEPROPERTYEX('TestingPersistSample', 'EDITION'),
ServiceObjective = DATABASEPROPERTYEX('TestingPersistSample', 'ServiceObjective'),
MaxSizeInBytes = DATABASEPROPERTYEX('TestingPersistSample', 'MaxSizeInBytes');

To know the current space consumption of an Azure SQL Database you can run the following query:


SELECT SUM(reserved_page_count) * 8.0 * 1024 AS "Database size in bytes"
FROM sys.dm_db_partition_stats


On the following query we are using information provided by previous queries to measure how much free space (in bytes) is left based on the storage limit of the current tiers and the actual size on the database.

SELECT CAST(DATABASEPROPERTYEX('TestingPersistSample', 'MaxSizeInBytes') as decimal)
- SUM(reserved_page_count) * 8.0 * 1024 AS "Free space in bytes"
FROM sys.dm_db_partition_stats

Based on the "Free space in bytes" value received you can decide at what threshold scale up the tier of your database.
 

ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');


All SQL Commands on this article can be part of an Azure Automation Runbook as explained here.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.