SQL server tempdb

Question

Post reply

SQL server tempdb

Siten0308

SSCarpal Tunnel

Points: 4396
More actions
November 27, 2019 at 4:09 pm
Go to Answer
#3701598

Hello,

I currently right now have a tempdb that has grown but now with making some modifications and also the dev team making also approrpiate changes, we wont expect to see tempdb grow crazy proportion to 2.5 tb... however because of that, we have a lot of free space within the files that i want to free up and claim back on disk space because it also shares some database files for other databases.

so my question is... what is the recommended past experience on reclaiming tempdb space, do i shrink... OR do i create a new tempdb file in the properties in the files tab (or place in the properties pane) and once created, drop the other tempdb files that are huge... so far none of them have any data and i would assume to do this off hours to be safe... but what is everyone's past experience/recommendation?

thanks in advance.
Jeffrey Williams

SSC Guru

Points: 89773
More actions
November 27, 2019 at 5:51 pm
Answer
#3701623

The files should revert back to initial size on a restart of SQL Server - if that doesn't work, then you can shrink the files specifying the size (using DBCC SHRINKFILE(logical, size).

If I had inherited this system - I would request a new volume either as a mount point or drive letter. Once that is available I would then move the files to the new storage and restart SQL Server.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver15

https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

November 27, 2019 at 4:19 pm

#3701601

this is just an opinion.. I have a few servers that have tempdb that "misbehaves".

this is normally transactions that don't commit - or an index operation at the weekend

best practice for tempdb says - do not put it on the same physical drive as your data or log files

give us a bit more more data and we can help

MVDBA

Siten0308 SSCarpal Tunnel Points: 4396 More actions · Answer 2

thanks Mike for the info, the brief background was poor written store procedures that made many many unnecessary tempdb but after finally reviewing it, i made some changes, tested and now its in prod.

Yes i agree to keep everything seperate drives (data files, system files, tempdb) however i did not choose this setup, like you and many others, i inherited it... let me know what other information you are asking to know to give advice weather i should create new tempdb files, and drop the old, OR if i should just shrink the tempdb (i dont like shrinking data files... but for tempdb, if i have to i have to).