Decrease Elastic Pool Storage

Question

Hello,

Yesterday i have out of space of elastic pool storage, then i add 50GB (from 200GB to 250GB) to the storage and i truncate some tables. After i truncate the table my storage utilization back to normal (105 GB).

Then i want to decrease the storage back to 200 GB but i always got this error even on the elastic pool overview the storage used is 105 GB. Anyone know why?

Here elasticpool overview

Answer 1

Hi Handian Sudianto,

Thanks for reaching out to Microsoft Q&A.

Please consider the following for narrowing down the issue.

1. The metrics that report storage usage within Azure SQL may not update immediately. Although you've truncated tables and physically reduced the amount of storage being used, the system metrics that azure SQL uses to determine if a decrease in storage capacity is permissible might not yet reflect these changes.
2. SQL Server often reserves space for future growth and operations, which might not be immediately freed up in the storage metrics after data deletion or truncation. This reserved space can affect your ability to reduce the size of the storage pool.
3. If the elastic pool manages multiple databases, decreasing the storage might require data to be redistributed within the pool. The system might prevent storage reduction if it anticipates potential data distribution constraints or if not all databases within the pool have reduced their footprint equally.
4. Remember that Azure also considers snapshot and backup storage in its capacity calculations. If there are snapshots or backups that haven't been cleaned up or reduced, this could be affecting the overall storage calculation.

Possible steps to resolve:

• Give the system some time to update its metrics. Monitor the storage usage metrics via the Azure portal to see if they adjust over the next few hours.
• Check any backups or snapshots that may be contributing to the storage usage.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.