Manage storage capacity
We will learn how to manage storage capacity in Azure SQL Database by defining several types of storage space for databases. Also, when the file space allocated needs to be explicitly managed, there are steps that can be taken.
There are workload patterns in the Azure SQL Database where the allotment of underlying data files for databases might exceed the number of consumed data pages. This circumstance can arise when the amount of space consumed grows and data is erased as a result. Furthermore, the reason for this is that when data is erased, the allotted file space is not instantly recovered.
Monitoring file space usage and shrinking data files may be necessary in the following scenarios:
- Firstly, allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
- Secondly, allow decreasing the max size of a single database or elastic pool.
- Lastly, allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.
Understanding types of storage space for a database
Understanding the following storage space amounts is critical for managing a database’s file space.
Query a single database for storage space information
The following queries can be used to determine storage space quantities for a single database.
Database data space used
Modify the following query to return the amount of database data space used. However, units of the query result are in MB.
SQL
— Connect to master
— Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = ‘db1’
ORDER BY end_time DESC
Database data space allocated and unused allocated space
Use the following query to return the amount of database data space allocated and the amount of unused space allocated. Units of the query result are in MB.
SQL
— Connect to database
— Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
SUM(size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = ‘ROWS’
Database data max size
Modify the following query to return the database data max size. However, units of the query result are in bytes.
SQL
— Connect to database
— Database data max size in bytes
SELECT DATABASEPROPERTYEX(‘db1’, ‘MaxSizeInBytes’) AS DatabaseDataMaxSizeInBytes
Understanding types of storage space for an elastic pool
Understanding the following storage space quantities are important for managing the file space of an elastic pool.
Query an elastic pool for storage space information
The following queries can be used to determine storage space quantities for an elastic pool.
Elastic pool data space used
Modify the following query to return the amount of elastic pool data space used. However, units of the query result are in MB.
SQL
— Connect to master
— Elastic pool data space used in MB
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = ‘ep1’
ORDER BY end_time DESC
Elastic pool data space allocated and unused allocated space
To return a table indicating the allotted and unused allocated space for each database in an elastic pool, modify the following examples. The table, on the other hand, lists databases in order from those with the most unused allotted space to those with the least amount of unused allocated space. The query result’s units are in megabytes.
Furthermore, the query results for finding the space provided for each database in the pool may be put together to get the elastic pool’s overall space allocation. The elastic pool area allotted, however, must not exceed the elastic pool’s maximum size.
Elastic pool data max size
Modify the following T-SQL query to return the elastic pool data max size. However, units of the query result are in MB.
SQL
— Connect to master
— Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = ‘ep1’
ORDER BY end_time DESC
Reference: Microsoft Documentation