question

sakuraime avatar image
0 Votes"
sakuraime asked SeeyaXi-msft commented

SQL Server 2016 tempdb size

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered sakuraime commented

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.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

some 256MB are old files. some newer added files growth beyond 256MB .

0 Votes 0 ·

Hi @sakuraime,

You said the file sizes are different, these files were not added together at the same time, right? The first files will get bigger as space is used, and the new files added later are the initial size at first, so the files added later will look smaller. If the tempdb files are too big, you can restart the sql server and they will go back to the initial size.
By the way, you can check if you have set the maximum value for individual files. For example, if a file is set with a maximum value of 1GB, it will be allocated space proportionally at first, but since it has reached the maximum value (1GB), it will not grow any more and will take proportional space in the remaining files.


Best regards,
Seeya

0 Votes 0 ·

but one thing strange is the later added files growth larger than the current files ....

0 Votes 0 ·
Show more comments
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft commented

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.


1.png (46.3 KiB)
2.png (14.7 KiB)
3.png (16.6 KiB)
4.png (16.3 KiB)
5.png (15.2 KiB)
6.png (27.1 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks for your explanation , but how do you explain there is one , or some files growth much more than the others ?

0 Votes 0 ·

and is that your example is perform on SQL 2016 ?

0 Votes 0 ·

Hi @sakuraime,

I tested it on SQL 2019, but I don’t think there is a problem with the version.
Here is a script to calculate the remaining space of the tempdb file, which can help you check the space on your own database.

 USE tempdb
 GO
 SELECT b.groupname AS 'File Group'
     ,a.NAME
     ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)]
     ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)]
     ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) 
 AS [Available Space (MB)]
 FROM sys.database_files a(NOLOCK)
 LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid
 ORDER BY (CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100)
  / (CONVERT(INT, ROUND(a.Size / 128.000, 2)))

how do you explain there is one , or some files growth much more than the others

In theory, it should meet a space proportional distribution algorithm. If you feel that it is still wrong, you should restart sql server and observe.

If you think my answer is helpful to you, please click "Accept Answer". This will help more people who also encounter this problem. Thanks for your contribution.


Best regards,
Seeya

0 Votes 0 ·
Show more comments