First published on MSDN on Feb 08, 2019
There are multiple ways to achieve this and there are also some storage types you should be aware
There is a good doc 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.
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 , listed as Total Database Size , at this moment represents Used Space .
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"
}
There are multiple ways to achieve this and there are also some storage types you should be aware
There is a good doc 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 , listed as Total Database Size , at this moment represents Used Space .
- Its NOT provided in this chart the allocated space
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"
}
Updated Mar 14, 2019
Version 2.0
