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

Carston 0 Reputation points
2024-04-18T16:30:26.5966667+00:00

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?

Azure SQL Database
Azure
Azure
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
955 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2024-04-18T22:47:58.6166667+00:00

    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.

    0 comments
  2. Rahul Randive 8,521 Reputation points Microsoft Employee
    2024-04-19T00:55:20.72+00:00

    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!

    0 comments
  3. Carston 0 Reputation points
    2024-04-24T17:30:49.0733333+00:00

    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.

    @Rahul Randive 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.

    0 comments
  4. Carston 0 Reputation points
    2024-04-24T20:10:48.96+00:00

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

    0 comments
  5. Carston 0 Reputation points
    2024-04-25T17:50:49.2833333+00:00

    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