Q: Can we change the growth increment of tempdb from 10% to 1 GB?
A: Yes. To do this, stop SQL Server, on command prompt: sqlservr -c -f. Doing this reset the TempDB data file to 2,304 KB (not 1 MB as in KB 307487) and the log file to 504 KB. The growth is reset to 10% MB for data and 1 MB for log. SQL is in single user mode, so no risk of TempDB corruption. No, from Config Mgr you can’t see that SQL Server is running. Then do this from SSMS:
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev’, FILEGROWTH = 1GB)
Verify the growth increment before and after by issuing “sp_helpdb tempdb”. The growth column for the log file should be 10% before and 1024KB after.
Control-C on the command prompt to stop SQL Server. Restart SQL Server from Config Mgr.
Q: Can we shrink the TempDB?
A: Yes, we can. Same as above. Stop SQL Server and restart in single user mode on command prompt. But we specify the size this time, like this:
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev’, SIZE = 3GB)
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘templog’, SIZE = 1GB)
Note: we can only go higher but can’t go lower. For example, after setting it to 5 GB we can set it to 6 GB but we can’t set it to 4 GB. If we try to set it to 4GB it will remain 5GB.
We can specify the growth and the size at the same time.
Q: My TempDB is 200GB. If I shrink it to 1 GB will it come back to 200 GB?
A: If you execute the same queries against the same data, yes. If the big query that caused the TempDB to blow to 200GB is only a one off, no.
Q: Is it worth shrinking TempDB?
A: Depends on your disk space. If you have ample disk space then you shouldn’t worry. But question like this usually comes up because the disk space is limited. One point I’d like to make is, if you are working with a 1TB warehouse and running queries against a 1-billion-rows fact table, with joins to 5 dims and “sum group by” etc, don’t be surprise if the TempDB is 200GB. True that most SQL DBAs who never administered a SQL warehouse would be surprised, as in their books a TempDB “should be” around 5-10GB. Yes, the TempDB of a SQL Server used for warehousing is different to if it’s used for “data entry” applications (known as “transaction systems” in the BI world).
If you are working with a SQL warehouse, the question should be more of: how do I optimise the TempDB performance rather than disk space, e.g. 1 TempDB file per processor core, placing TempDB files on different spindles, putting them on RAID 1+0 rather than RAID 5, etc. If your company can’t buy a 1TB disk space then they probably shouldn’t do data warehousing. Almost by definition, data warehousing is about disk space.
Q: Can I shrink TempDB without stopping SQL Server?
A: Yes. Use dbcc alter file/db. Consider the risk of data corruption. This risk does look scary for prod DBs but may be acceptable for dev.