Hi Jad Tabet •,
There could be several reasons why the SQL Server Tempdb database is not shrinking. Here are some possible reasons
- Active Transactions: If there are active transactions in the Tempdb database, it will not shrink. You can check for active transactions by running the following query:
SELECT * FROM sys.dm_tran_active_transactions
If there are active transactions, you will need to wait for them to complete before attempting to shrink the Tempdb database.
- Auto-growth Settings: If the auto-growth settings for the Tempdb database are set too low, it can cause the database to grow quickly and not shrink. You can check the auto-growth settings by running the following query:
SELECT name, is_percent_growth, growth FROM sys.database_files
If the growth value is too low, you can increase it to a higher value to prevent the database from growing too quickly.
- Open Transactions: If there are open transactions in the Tempdb database, it will not shrink. You can check for open transactions by running the following query:
DBCC OPENTRAN('tempdb')
If there are open transactions, you will need to either commit or rollback the transactions before attempting to shrink the Tempdb database.
- Internal objects: Tempdb contains internal objects that cannot be removed or shrunk. These objects include system tables, allocation pages, and other internal structures. If these objects are taking up a significant amount of space, it may not be possible to shrink the database.
Please find a blog on similar line
https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/
Thank you!