Deleting TempDB files

Off Topic
1

Since today has become Performance Tuning Day for me, I wanted to ask about TempDB files.

The PDT and this post from Nathan both advise using 8 TempDB files of equal size.

So, somewhere along the way we messed that up, and I REALLY hate that the names don’t match - regardless of if that actually matters or not.

Question is this: can I delete all and start over?

Is Nathan’s script sufficient or is this too much of a mess (see pic)?

I’m assuming all DBs should be offline or something when I do this.

image
image689×625 44.1 KB

2

Yes, kind of. You can use a script like Nathan’s to alter tempdb, and then when SQL Server restarts the tempdb is recreated with the alterations (tempdb is killed and resurrected every time SQL server service restarts).

I like the Brent Ozar strategy for tempdb: https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

Dedicate a separate drive (on the fastest storage you have) and just fill 90% of it up with tempdb and turn auto growth off. Make it much bigger than it needs to be, set and forget. If some rogue out of control process fills up tempdb one day then it’s probably for the best that you’re not letting it autogrow which might tank the whole server. If some routine process is filling up tempdb then you have sized it wrong. In my experience database integrity check and index maintenance are the two routine processes that fill up tempdb the most.

I would think for most on prem customers 100gb is way more than enough for the tempdb drive. Give each file 8 or 9 gb. Definitely overkill, but if you can spare 100gb of storage, who cares?

1 Like
3

Perfect, thank you.