Standalone/Serverless Azure DB - DBCC SHRINKDATABASE and DBCC SHRINKFILE throwing "Query execution timed out." after 300 seconds

Question

I have a standalone DB and trying to use "Query Editor (Preview)" to shrink the DB or DB file. It has 500 GB max storage, 410 GB allocated, any only 152 GB used. I want to lower the allocated so that I can also lower the max. Every query related to shrink ends in a time out. Any suggestions to resolve this?

Answer 1

As a workaround, try to connect to the Azure SQL database you want to reclaim space and run the following T-SQL statement no enable auto-shrink.

ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

Once you have reclaimed the free space disable auto shrink and defragment your indexes. The shrink operation creates fragmentation on indexes and that can have an impact on query performance.

You can also try to execute the DBCC commands from SQL Server Management Studio or Azure Data Studio. Query Editor is no for those DBCC commands that can consume hours or days executing depending on the size of the database.

Answer 2

Hi @Carston

Shrink commands impact database performance while running, and if possible, should be run during periods of low usage.

When database allocated space is in hundreds of gigabytes or higher, shrink may require a significant time to complete, often measured in hours.

Please try shrinking it in smaller chunks because shrinking is a single-threaded operation and is very IO intensive. You can shrink with 1 GB or 10 GB to start with. Also, you have to ensure that there are no transactions running on the database at the time of shrink as they could cause the shrink operation to fail. Please use SSMS to perform shrink operation.

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

Thank You!

Answer 3

Thanks @Alberto Morillo , I turned on auto shrink, but after an hour, no change in allocated space yet. I will check back on that later. Since this is a standalone DB in Azure, I cannot access it through SQL MS, just the "Query editor (Preview)" in Azure.

@RahulRandive I ran
DBCC SHRINKFILE (1, 40900);
To attempt to shrink the allocated space by 1 GB, but it still timed out after 300 seconds.

This is an unused database that is just hanging around until we move onto a new monitoring platform, keeping it around in case the new one does not work out and we have to go back to this db. It idles at 0% DTU usage, until I try and run a query.

Is there any way to increase the 300 second timeout? EDIT: Just found out about Azure Data Studio, just installed that to workaround the timeout, waiting to see if that worked.

Answer 4

Just found Azure Data Studio to connect to the db, which is working around the 300 second timeout, will report back with results.

Answer 5

Still not able to lower allocated space. I disabled auto shrink, but no matter what I do, I get the following:

File ID 1 of database ID 5 cannot be shrunk as it is either being shrunk by another process or is empty.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Total execution time: 00:35:41.109