Reclaiming free space from TempDB in azure sql managed instance

Question

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

Answer 1

@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

Answer 2

Hi,HusainIbrahem-6130

You can set the size of tempdb by following the tutorial below.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/tempdb-configure?view=azuresql-mi&tabs=ssms