[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

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



Analysis Services
CLR Integration
High Availability
Open Source
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019

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.


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.