AzureFeeds

This week I worked on a service request where our customer needed to understand how an Azure SQL Database had grown over time. This information can be useful for capacity planning, cost analysis, and performance reviews.

There are several possible approaches, depending on whether we need to review recent historical data that is still available in Azure Monitor, or whether we need to start collecting long-term historical data from now on.

In this lesson learned, I would like to summarize some of the options available.

1. Reviewing recent historical data using Azure Monitor metrics

The first point to clarify is how Azure Monitor metrics retention works.

Most Azure platform metrics are retained for up to 93 days. However, a single Azure Monitor Metrics chart can query no more than 30 days of data at a time. This means that, if the data is still within the Azure Monitor retention window, we might need to review the metric in 30-day intervals.

For Azure SQL Database storage usage, the metric commonly used is Data space used

2. Exporting metrics to Log Analytics for long-term analysis

If the requirement is to perform long-term analysis, I would like to recommended option is to enable Diagnostic Settings on the Azure SQL Database and send the metrics to a Log Analytics workspace.

Azure SQL Database diagnostic telemetry can be exported to different destinations, including:

• Log Analytics workspace


• Storage Account


• Event Hubs

Using Log Analytics provides a very flexible way to query, aggregate, and visualize the data by using KQL.

Once the metrics are available in Log Analytics, we can calculate the monthly database growth. For example:

AzureMetrics
| where ResourceProvider =~ “MICROSOFT.SQL”
| where ResourceId == “/SUBSCRIPTIONS/your subscription/RESOURCEGROUPS/yourresourcegroup/PROVIDERS/MICROSOFT.SQL/SERVERS/yourserver/DATABASES/yourdatabase”
| where MetricName == “storage” | summarize arg_max(TimeGenerated, Average) by Month = startofmonth(TimeGenerated)
| project Month, DataSpaceUsedGB = round(Average / 1024 / 1024 / 1024, 2)
| order by Month asc

This query takes the last available value for each month and converts the metric from bytes to GB. Depending on the analysis requirements, the query can be customized. 

3. Creating a custom database space usage history process

If we need more control, or if we want to collect more granular database-level information, another option is to create a custom process that periodically captures the current database space usage into a table. This approach can be useful when we want to keep the information inside the database itself and avoid depending on external telemetry storage for this specific requirement.

For example, the following table can be used to store daily or weekly snapshots:

CREATE TABLE dbo.DatabaseSpaceUsageHistory
(
SnapshotTimeUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
DatabaseName sysname NOT NULL,
DataAllocatedMB decimal(19,2) NULL,
DataUsedMB decimal(19,2) NULL,
DataUnusedMB decimal(19,2) NULL,
LogAllocatedMB decimal(19,2) NULL
);

–Example collection query:
INSERT INTO dbo.DatabaseSpaceUsageHistory
(
DatabaseName,
DataAllocatedMB,
DataUsedMB,
DataUnusedMB,
LogAllocatedMB
)
SELECT
DB_NAME() AS DatabaseName,
SUM(CASE WHEN type_desc = ‘ROWS’ THEN size END) * 8.0 / 1024 AS DataAllocatedMB,
SUM(CASE WHEN type_desc = ‘ROWS’ THEN FILEPROPERTY(name, ‘SpaceUsed’) END) * 8.0 / 1024 AS DataUsedMB,
(
SUM(CASE WHEN type_desc = ‘ROWS’ THEN size END)
– SUM(CASE WHEN type_desc = ‘ROWS’ THEN FILEPROPERTY(name, ‘SpaceUsed’) END)
) * 8.0 / 1024 AS DataUnusedMB,
SUM(CASE WHEN type_desc = ‘LOG’ THEN size END) * 8.0 / 1024 AS LogAllocatedMB
FROM sys.database_files;

This process can be executed daily, weekly, or monthly using the automation method that best fits the environment. This approach provides more control over the data collected, the retention period, and the frequency of collection.