MSsql change tempdb default size

CAD CAD Admin (Install, PDM, etc)
1

Is there a way to change the initial size for tempdb files while the server is running?

I do not mind to wait until the next reboot to take effect, but I do not want to stop the server to change it.

2

I am not a DB expert, I found the following information on the web.

  1. MSsql tempdb CURRENT SIZE and INITIALIZATION SIZE can be managed separately, if you do not need to shrink the current tempdb, it can be done and the next database service restart will recreate tempdb according to its new initialization size

  2. Tempdb initialization size is stored in sys.master_files, while tempdb.sys.database_files stores the current state of tempdb

  3. When using SSMS to resize tempdb to a file size smaller than current setting, it will try to shrink the current file at the same time, but if one of the tempdb files is in use it will prevent the shrink operation from completing

  4. T-SQL for modifying the INITIALIZATION SIZE of multiple tempdb, from SSMS check the logical name of each tempdb file (not the file name which is in another column)

e.g. tempdb has 8 files, current size is fixed to 8GB each no autogrowth.
Next reboot I want to resize them at 3GB each

G:\tempdb\tempdb.mdf
Logical name: tempdev
G:\tempdb\tempdb_mssql_2.ndf
Logical name: temp2
G:\tempdb\tempdb_mssql_3.ndf
Logical name: temp3
G:\tempdb\tempdb_mssql_4.ndf
Logical name: temp4
G:\tempdb\tempdb_mssql_5.ndf
Logical name: temp5
G:\tempdb\tempdb_mssql_6.ndf
Logical name: temp6
G:\tempdb\tempdb_mssql_7.ndf
Logical name: temp7
G:\tempdb\tempdb_mssql_8.ndf
Logical name: temp8

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'tempdev', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp2', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp3', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp4', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp5', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp6', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp7', SIZE = 3GB )

ALTER DATABASE tempdb 
 MODIFY FILE ( NAME = N'temp8', SIZE = 3GB )

The new initialization size will take effect at the next SQL restart.

3

Just curious, what benefit is there to controlling its size? I’m not a db guy either, just learned what I can since we got PDM in 2009 as we had no one internal that knew much about it either.

4

I cannot properly answer as I am not an expert on the topic, but we had a tempdb problem with PDM and big queries year ago with the file filling a whole disk on the server apparently.
since then the file was set to autogrowth and it went up in size, little by little, from 10Gb to 20Gb in around 5 years.
Now we are on a new server with SQL2022, same hardware.
I set tempdb to 8 static files of about 8Gb each and a log of 25Gb on a dedicated partition, no autogrowth.
Problem is sql is caching thw whole temp db taking 50GB of memory in the process.
I have 10GB reserved for the OS and the rest allocated to sql to be used.
temdb usage is not that big apparently so I am trying to tune the server looking at how it responds.

I have also a test server with similar specs, to check the settings before production.