How to get Azure SQL database size

This is a copy of original post I wrote to Azure SQL DB Support blog. Also updated with some news in Azure Metrics

https://blogs.msdn.microsoft.com/azuresqldbsupport/2019/02/08/how-to-get-azure-sql-database-size/

There are multiple ways to achieve this and there are also some storage types you should be aware

There is a good documentation about this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management

Understanding the following storage space quantities are important for managing the file space of a database.

• Data space used
  • The amount of space used to store database data in 8 KB pages. Generally, space used increases (decreases) on inserts (deletes). In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. For example, deleting one row from every data page does not necessarily decrease the space used.

• Data space allocated
  • The amount of formatted file space made available for storing database data. The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted.

• Data space allocated but unused
  • The difference between the amount of data space allocated and data space used. This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.

• Data max size
  • The maximum amount of space that can be used for storing database data. The amount of data space allocated cannot grow beyond the data max size.

There are multiple methods to query this data

In the Portal is the easiest way to get this for a single database in the overview blade

Or in the database Metrics blade you can also check the historical information: Data Space Allocated and Data Space Used

You can also query master or directly the database using TSQL

-- 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

OR

-- 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'

OR

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes

For an elastic pool

-- 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

OR

-- 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

OR

Connect-AzureRmAccount

# Resource group name
$resourceGroupName = "rg1" 
# Server name
$serverName = "ls2" 
# Elastic pool name
$poolName = "ep1"
# User name for server
$userName = "name"
# Password for server
$password = "password"

# Get list of databases in elastic pool
$databasesInPool = Get-AzureRmSqlElasticPoolDatabase `
                        -ResourceGroupName $resourceGroupName `
                        -ServerName $serverName `
                        -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# For each database in the elastic pool,
# get its space allocated in MB and space allocated unused in MB.

foreach ($database in $databasesInPool)
{
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    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'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
    (Invoke-Sqlcmd  -ServerInstance $serverFqdn `
                    -Database $database.DatabaseName `
                    -Username $userName `
                    -Password $password `
                    -Query $sqlCommand)
}

# Display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort `
    -Property DatabaseDataSpaceAllocatedUnusedInMB `
    -Descending | Format-Table

If you want to collect the database size without connecting directly to SQL Server you can query Azure Metrics, as said above (Total Database Size, at this moment represents Used Space)

Connect-AzureRmAccount

function Get-TotalDatabaseSizeKb
{
    param($Resource)
    $Result = $Resource | Get-AzureRmMetric -MetricName 'storage' -WarningAction SilentlyContinue
    $DBSize = $Result.Data[$Result.Data.Count-2].Maximum
    $DBSize / 1024
}

#######################################################################
#Single DB
$DB = Get-AzureRmResource -ResourceGroupName "GROUPNAME" -Name "SERVER/DATABASENAME"
$DBSize = Get-TotalDatabaseSizeKb $DB
"DB ($($DB.Name)) $($DBSize)Kb or $($DBSize / 1024)Mb"

#######################################################################
#All DB
$Databases = Get-AzureRmResource -ResourceGroupName "GROUPNAME" -ResourceType Microsoft.Sql/servers/databases
foreach ($DB in $Databases)
{
    $DBSize = Get-TotalDatabaseSizeKb $DB
    "DB ($($DB.Name)) $($DBSize)Kb or $($DBSize / 1024)Mb"
}

*Update 2019-04-03: You can get all metric names using

Get-AzResource -ResourceGroupName "ResGroupName" -Name "server/db" -ResourceType Microsoft.Sql/servers/databases/metricDefinitions -ExpandProperties | Select Name

Advertisement