SQL Server 2016 tempdb size

sakuraime 2,336 Reputation points
2021-10-28T02:02:52.587+00:00

I have a sql server 2016 with 16 tempdb file. initial size is 256MB , and all file growth auto growth is 256MB .

I found some days later the file size are not equal , some are 10GB , and some still 256MB .

file should be growing by proportional fill, so why difference size would be happen ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,476 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Seeya Xi-MSFT 16,571 Reputation points
    2021-10-28T08:05:13.453+00:00

    Hi @sakuraime ,

    The data files grow in proportion to the free space of the files. I have done a test to prove that the Tempdb file is also proportionally increased in this way.
    As for your "still 256MB", I want to confirm with you whether you later added the tempdb file yourself. I have a guess that "Some are 10GB" are the files that you have used before and the space has grown. Because the newly added file is the initial size.
    Since tempdb will return to its initial size after restarting SQL Server, I suggest you restart SQL Server and then observe whether there is an abnormal growth.
    Find the instance in the SQL Server Configuration Manager and right click on ‘Restart’.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Seeya Xi-MSFT 16,571 Reputation points
    2021-10-29T04:26:24.397+00:00

    Hi @sakuraime ,

    Good day!
    Since the comment is too long, let me talk about it here.
    Tempdb grows automatically when you use sqlserver. For example, you need to store temporary variables when you create a temporary table. This increase is in proportion to the remaining space of the Tempdb file.
    I did an experiment:
    First of all, this is the initial look of my Tempdb file. For experimentation, I limited the maximum value of a file.
    144813-1.png
    Then I inserted a lot of temporary data to see the changes in the size of the Tempdb file.
    144777-2.png
    Because temp2 has a maximum value, it will grow to 100MB, and it will not grow. When space is still needed, it will continue to grow proportionally in other files.
    Then I added a temp file.
    144778-3.png
    Continue to insert temporary data:
    144779-4.png
    The file temp9 has already exceeded the previous limit size of temp2. Look at the available space, they are the same, so each time the growth situation will be the same proportional growth. In this experiment, the available space is 21MB, so they will all increase by the same value next time.
    144835-5.png
    As you can see, they have indeed increased the same ratio, both of which are 320MB.
    After restarting the sql server, you can see that they are back to the initial size.
    144730-6.png
    Hope the above can help you.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it.


Your answer

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