Forum Discussion
Tempdb storage on the disk
I'm working with SQL 2019 on-premises. lately due to an unoptimized query the size of tempdb spikes to around 49 GB and nearly 8 GB for each files group. now query is optimized and no longer spills to tempdb but we cannot fix the size of tempdb. I tried restarting the SQl server.
I would like to ask whether this size is necessary and how can I reduce the size of tempdb.
- if you modify the filesize for each file without doing shrink file the script should come back immediately but you won't see the result until restarting the sql server instance
Regards
Javier
3 Replies
Hello
You should modify the file size and then restart the instance. do not try to shrink them
Example
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], SIZE = 512000KB, FILEGROWTH = 102400KB)
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], SIZE = 512000KB, FILEGROWTH = 102400KB)
...
...
Regards
Javier
@Javier Villegas actually I ran this query, but it takes more than 12 minutes to execute and at the end it made me to stop the query without any resizing. Should clear the cache by running the query: DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS. followed by DBCC FREEPROCCACHE WITH NO_INFOMSGS.
my concern is the impact to the performance.
best regards.
- if you modify the filesize for each file without doing shrink file the script should come back immediately but you won't see the result until restarting the sql server instance
Regards
Javier
