Forum Discussion

Copper Contributor

Dec 12, 2023

Solved

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 t...

sql server

tempdb

Javier_Villegas
Dec 13, 2023
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

Javier_Villegas

MVP

Dec 13, 2023

Arghavan300

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

Arghavan300

Copper Contributor

Dec 13, 2023

@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.

Javier_Villegas
MVP
Dec 13, 2023
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