Unable to Reclaim Allocated Space After Shrinking Azure SQL Database

Question

Unable to Reclaim Allocated Space After Shrinking Azure SQL Database

Dattatray Giramkar 0

We are experiencing an issue with reclaiming allocated space from our Azure SQL Database (Pass Service)despite running DBCC SHRINKDATABASE. The database in question (db_name) currently shows approximately 300 GB of used space but over 1 TB of allocated space.

We have taken the following steps:

Verified free space using sys.database_files and FILEPROPERTY(..., 'SpaceUsed')
Attempted shrink using DBCC SHRINKDATABASE ([db_name]);
Enabled AUTO_SHRINK via ALTER DATABASE [db_name] SET AUTO_SHRINK ON;

Confirmed there are no blocking transactions during the shrink attempt

Observed the operation starts but does not reduce file size significantly

Azure portal still reports high allocated space, affecting our storage cost

Our goal is to free up approximately 600 GB of unused space and reduce the storage billing. We would like to understand:

Why the allocated space is not being released after shrink

Whether any limitations exist in Azure SQL PaaS for reclaiming space

How to reduce the max allocated storage reflected in billing

Please advise on how to proceed, and whether a manual intervention from Microsoft is needed to reset or reclaim unused space.

Thank you.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-14T15:25:04.2+00:00

First of all, turn off AUTO_SHRINK immediately. That is an evil option, and will only cause you pain.

Next, you need to determine whether it is the data file(s) that are large or the file, and the apply DBCC SHRINKFILE on the file in question. Shrinking the log file and shrinking the data file are two quite different things.

Is this General Purpose of Hyperscale?
Dattatray Giramkar 0 Reputation points

2025-06-16T08:42:02.5966667+00:00

Thanks for the reply.
I am using General Purpose .

1 answer

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-14T15:25:04.2+00:00

First of all, turn off AUTO_SHRINK immediately. That is an evil option, and will only cause you pain.

Next, you need to determine whether it is the data file(s) that are large or the file, and the apply DBCC SHRINKFILE on the file in question. Shrinking the log file and shrinking the data file are two quite different things.

Is this General Purpose of Hyperscale?
Dattatray Giramkar 0 Reputation points

2025-06-16T08:42:02.5966667+00:00

Thanks for the reply.
I am using General Purpose .

Answer 1

Hi Dattatray Giramkar

Greetings!

In addition to the response provided by @Erland Sommarskog, I would like to add the following information.

As we understand that, you are facing a challenge with reclaiming space after shrinking your Azure SQL Database.

Here are some steps you can take to address this issue:

As suggested by @Erland Sommarskog in the first response, turn off the AUTO_SHRINK option right away. It's generally not advisable since it can lead to performance issues.

Determine file size whether it's the data files, the log file, or both that are large. You can check the size by querying:

SELECT file_id,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024 AS space_used_mb,
CAST(size AS bigint) * 8 / 1024 AS space_allocated_mb,
 CAST(max_size AS bigint) * 8 / 1024 AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

This helps you understand the file layout and identify which file(s) need shrinking.

Instead of DBCC SHRINKDATABASE, consider using DBCC SHRINKFILE on the specific data file that you want to reduce:

DBCC SHRINKFILE (your_data_file_name, TRUNCATEONLY);

This method is often more effective and allows targeted shrinking.

Sometimes, long-running transactions can prevent the shrink operation from completing. You can use the following query to identify such transactions:

SELECT * FROM sys.dm_exec_requests WHERE status = 'running';

Terminating these transactions if safe to do so (caution advised) could help succeed in shrinking.

If you run a shrink command, it can lead to fragmentation. After shrinking, you may want to conduct index maintenance (rebuild or reorganize) to ensure optimal performance:

ALTER INDEX ALL ON your_table_name REBUILD;

Be aware that even after shrinking, your database may still show high allocated space until the next billing cycle. There’s typically no immediate adjustment on the billing side until usage patterns change.

We would like to inform you that, there are generally no specific limitations beyond those imposed by your service tier. However, the behavior might vary slightly between service tiers (like General Purpose vs. Business Critical).

I would request you to refer the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql-db

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver17&source=recommendations

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver17&source=recommendations

https://dba.stackexchange.com/questions/325901/how-to-reclaim-space-on-sql-server-azure

I hope, This response will address your query and helped you to overcome on your challenges. If this answers your query, do click Accept Answer and Yes for was this answer helpful.

If you still have questions, please let us know what is needed in the comments so the question can be answered.

Thank you for helping to improve Microsoft Q&A!

Dattatray Giramkar 0 Reputation points

2025-06-16T08:49:56.2433333+00:00

Thanks for the reply.
Currently, I need to free up the allocated space. Please refer to the image below as you can see, there is a difference between the used space and the allocated space.

I tried to reduce the storage size but encountered the error shown. Kindly check the attached screenshot.

Screenshot 2025-06-16 141745.png

Screenshot 2025-06-16 141649.png
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-16T21:23:46.5633333+00:00

What do you see if you run this query:

SELECT size*8192/1024/1024, * FROM sys.database_files

Here is a query that shows you the sizes of your tables in descending order:

; WITH CTE AS ( SELECT s.name AS schema_name, o.name AS object_name, p.rows, (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = o.object_id) AS colcnt, CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.total_pages ELSE 0 END * 8192 / 1000000 AS regular_MB, CASE WHEN au.type_desc = 'LOB_DATA' THEN au.total_pages ELSE 0 END * 8192 / 1000000 AS LOB_MB, CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.used_pages ELSE 0 END * 8192 / 1000000 AS used_regular_MB, CASE WHEN au.type_desc = 'LOB_DATA' THEN au.used_pages ELSE 0 END * 8192 / 1000000 AS used_LOB_MB FROM sys.schemas s JOIN sys.objects o ON s.schema_id = o.schema_id JOIN sys.indexes i ON o.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.allocation_units au ON au.container_id = CASE WHEN au.type IN (1, 3) THEN p.hobt_id WHEN au.type = 2 THEN p.partition_id END WHERE i.index_id IN (0, 1) ) SELECT schema_name, object_name, MAX(colcnt) AS no_of_columns, MAX(rows) AS no_of_rows, SUM(regular_MB + LOB_MB) AS total_MB, SUM(used_regular_MB + used_LOB_MB) AS used_MB, SUM(regular_MB) AS regular_MB, SUM(LOB_MB) AS LOB_MB, SUM(SUM(regular_MB)) OVER () FROM CTE GROUP BY schema_name, object_name ORDER BY total_MB DESC, no_of_rows DESC
Vijayalaxmi Kattimani 3,065 Reputation points Microsoft External Staff Moderator

2025-06-18T01:33:44.96+00:00

Hi Dattatray Giramkar
We haven’t heard from you on the last response provided by Erland Sommarskog and wanted to follow up to check if your issue has been resolved.

If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
Dattatray Giramkar 0 Reputation points

2025-06-18T09:06:22.3533333+00:00

Hello @Vijayalaxmi Kattimani

I have followed the process you shared and executed the recommended command on the Azure database. However, despite trying both approaches, I am still unable to reclaim the unused space.

Screenshot 2025-06-16 141745.png

Share via

Unable to Reclaim Allocated Space After Shrinking Azure SQL Database

1 answer

Your answer