how to get used space of an Azure SQL database using PowerShell?

Question

Hi. I need to check the Azure SQL database's 'used space' details and resize it according to the predefined formula. Even with several approaches, I couldn't get the space details. This code fetches the maximum storage size correctly but does not give the used space. I tried these ways. But the result was the same as I have explained here (getting max size correctly and showing '0' for used space).

Get-TotalDatabaseSizeKb

Get-AzMetric

May I know if there is any specific way to get these details?

$db_resource = Get-AzResource -ResourceId $database.ResourceId

            # Database used space
            $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage' 
            $db_UsedSpace = $db_metric_storage.Data.Average | Select-Object -Last 1
            $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)
			$db_MaximumStorageSize = $database.MaxSizeBytes / 1GB

Answer 1

You need to connect to Azure Metrics using Connect-AzureRmAccount.

You then need to create this function:

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

And use it as shown below:

#Size of all databases
$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"
}