Share via

SQL 2019 AlwaysOn instance - TempDB files out of balance

Cooper, David A. [ISS] (Contractor) 111 Reputation points
2025-12-06T17:45:17.5333333+00:00

Hello. I have a 3-node SQL 2019 AlwaysOn cluster which host multiple instance. One instance on primary node has TempDB files wildly out of balance. There are 8 files. TempDev = 14,285 MB. Temp2 - Temp8 = 8 MB each. On the secondary nodes, TempDB files for this instance are balanced in size. I am aware that I can failover primary node 1 to secondary node 2, and restart node 1 to rebalance TempDB. But I'd rather not do that at this time. Can I simply go into SSMS, properties of TempDB database, Files page and reset the TempDev file from 14,285 MB to 8 MB to match the other files? Thanks.

SQL Server Database Engine
0 comments

Answer recommended by moderator

  1. Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator
    2026-01-05T09:38:17.8766667+00:00

    Hi @Cooper, David A. [ISS] (Contractor),

    Shrinking tempdev to match the smaller files is not recommended and won’t resolve the imbalance. TempDB files only stay balanced when all data files can grow equally. In this case, SQL Server has clearly been forced to grow a single file to satisfy workload demand.

    The fix does not require failover or restart:

    • Verify that autogrowth is enabled and identical on all TempDB data files.
    • Proactively resize the smaller files so SQL Server no longer depends on a single file for allocations.

    Once file sizes and growth settings are aligned, TempDB will remain balanced going forward.

    Thanks,
    Akhil.

    Was this answer helpful?

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134K Reputation points MVP Volunteer Moderator
    2025-12-06T22:36:39.78+00:00

    First of all, I think you should investigate how you ended up here. Could it be that autogrowth has been turned off for the other tempdb files. If so, that is what you should fix first.

    Reducing tempdb to 8 MB does not sound like a good idea. Most likely you are using more than 8 MB of that file. Rather I would extend all the other files to the same size as tempdb.

    Was this answer helpful?

  2. Q&A Assist
    2025-12-06T17:45:28.1133333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    You cannot directly reduce the size of the tempdev file in SQL Server while the instance is running. The ALTER DATABASE command allows you to modify the size of the tempdb files, but it is generally recommended to do this in a controlled manner, especially in a production environment. If you attempt to resize tempdev down to 8 MB while it is currently at 14,285 MB, you may encounter errors if the current usage exceeds the new size.

    The recommended approach to rebalance tempdb files without failing over the instance is to restart SQL Server, which will recreate the tempdb files with the specified sizes. However, if you want to avoid a restart, you could consider adjusting the file sizes using the ALTER DATABASE command when the load on the database is low, but be cautious of the current usage before making such changes.

    For a more balanced setup, you might want to consider the following:

    1. Adjust the size of the tempdev file gradually, ensuring it does not exceed current usage.
    2. Monitor the usage of tempdb to ensure that it does not cause performance issues.
    3. Consider a future maintenance window where you can safely rebalance the tempdb files by restarting SQL Server.

    In summary, while you can attempt to adjust the size of tempdev, it is crucial to ensure that the new size does not conflict with current usage to avoid potential errors or performance issues.

    References:

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.