Share via

Tempdb Shrink Issue in SQL Server

Jad Tabet 0 Reputation points
2024-01-18T11:09:46.98+00:00

Dear Support Team, I need your consultancy regarding this issue: After shrinking the tempdb in Microsoft sql management studio the tempdb does not shrink and the disk size is not reclaimed even though the db is not being used only works after a restart of the server. Note that the server is on-prem. How can we solve this issue without restarting the server since it's a Production Server? Any advice? Best Regards,

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,179 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
62 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 42,746 Reputation points
    2024-01-18T11:39:27.5433333+00:00

    Dear Support Team,

    This is a community forum with volunteers. If you want to contact MS Support see https://support.microsoft.com/en-us It can happen that you can shrink the TempDB, because it's heavily in use or by some other reasons, see Shrink the tempdb database

    0 comments
  2. Rahul Randive 9,011 Reputation points Microsoft Employee
    2024-01-18T13:09:24.5766667+00:00

    Hi Jad Tabet •,

    There could be several reasons why the SQL Server Tempdb database is not shrinking. Here are some possible reasons

    1. 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.

    1. 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.

    1. 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.
    2. 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!
    0 comments
  3. Erland Sommarskog 105K Reputation points MVP
    2024-01-18T22:43:58.1666667+00:00

    even though the db is not being used

    If tempdb is not being used, you should have an issue with a restart, because that would mean that the entire server is unused! That is, tempdb is constantly in use for all sorts things: temp tables, sort spills, version store for snapshot and so on. It could be that your tempdb has the size it has, because it needs to have that size. If so, it is pointless to shrink it. Overall, shrinking database files of any kind is only meaningful if there has been an exceptional event that caused the file to explode. What is the current size of tempdb? What is the size of your biggest user database?

    0 comments
  4. ZoeHui-MSFT 34,756 Reputation points
    2024-01-19T07:30:37.6166667+00:00

    Hi @Jad Tabet,

    Here is a same thread you may also take a reference to.

    TempDB will not shrink. No Open Transactions

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments