TEMPDB adding files

Question

TEMPDB adding files

TRACEY-320982

SSChampion

Points: 13653
More actions
June 26, 2008 at 12:27 pm

#188521

I was thinking of adding more temp files as we have 8 CPU.
Right now only have the standard tempdev and templog.
How do you add more files.....and should it be set to autogrowth None.
What sizes to set i read 10024 for large and 2048 for log.
Once you add the files to TEMPDB - do you have to restart the system.
Cheers

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are a number of ways to do this. The one I find easiest is to go to tempdb in Management Studio (under System Databases), right click and go to Properties, then to Files, and click Add New.

The initial size should be fairly large. How large depends on expected use and disk space available, so you'll have to judge that. The numbers you give would work for a pretty busy system. Same goes for growth. If you do set up autogrowth, make it a large number, like a Gig or two at a time, to avoid file fragmentation.

It works best, of course, if they can be on different disks from each other. Best IO that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

I don't think you need to restart SQL, but I'm not sure how much benefit you get here. Unlike regular database files, you can't really control where tempdb objects are created.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

Thanks i added 8 of files for 8 CPU.

so i have tempdev 8

tempdev2 1024 up to

tempdev8

I cannot change the original one to 1024 says cannot change specified file size is less than current.

So rather than rebooting is there a way to change tempdev to 1024 so they are all same size 🙂

Toby White SSCertifiable Points: 6187 More actions · Answer 4

You could try the following, but I don't know if it will work with the tempdb files

use tempdb

dbcc shrinkfile(1,emptyfile)

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 5

If you start the server with the /f parameter, it creates a tempdb with a 1MB primary file ... you can then adjust it to the initial size that you require, shut down the server and restart it normally.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

How do you start the sql server with the /f command if it is part of a cluster.