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.
November 27, 2019 at 4:19 pm
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
November 27, 2019 at 5:15 pm
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).
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://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
November 27, 2019 at 8:25 pm
Great thank you Jeffrey for the info, very informative 🙂
November 28, 2019 at 11:00 am
I have faced same issue many times. Even row versioning enabled for the database which prevent the TempDB shrinking.
https://www.sqlserverblogforum.com/dba/tempdb-database-is-full/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply