Reclaiming free space from TempDB in azure sql managed instance

HusainIbrahem-6130 0 Reputation points
2024-05-17T12:34:07.53+00:00

the TempDB on my azure sql managed has grown to 350 GB but 99% of that space shows as unused.

i tried to shrink the database and individual files but the server is not freeing up the space. the space is still reserved for tempdb. Microsoft docs is suggesting a failover but i would rather not do that.

is there any alternative to reclaiming the space without a failover ?

and how can i prevent this from happening again ? is it a good idea to cap the autogrow limit of tempdb ?

Thank you

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,141 questions
{count} votes

2 answers

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 28,767 Reputation points Microsoft Employee
    2024-05-17T20:42:00.8633333+00:00

    @HusainIbrahem-6130 Welcome to Microsoft Q&A thanks for posting your question.

    SQL MI is the same as a SQL Server. The same methods of using combinations of DBCC commands might help. The web is full of the proposed solutions for this problem, but ultimately a failover will help if nothing else does.

    Here is the article which can be helpful in configuring the maximum size of TempDB in Azure SQL Managed Instance and how to configure the maximum size of TempDB using the ALTER DATABASE command, which allows you to set a maximum size for the TempDB data file(s) and prevent them from growing excessively.

    https://techcommunity.microsoft.com/t5/azure-sql-blog/configure-your-tempdb-max-size-in-azure-sql-managed-instance/ba-p/3715655

    Regards

    Geetha

    0 comments No comments

  2. MikeyQiaoMSFT-0444 1,415 Reputation points
    2024-05-20T09:48:07.2133333+00:00
    0 comments No comments