Temp DB Best Practice

Question

Temp DB Best Practice

aravind_0404

Say Hey Kid

Points: 680
More actions
July 14, 2009 at 4:05 pm

#139815

Hi,
We are building a new server planning migration from sql 2005 to sql 2008 on windows 2008 server.
we have an 16 processor,64 GB RAM,.Each Database has seperate SAN of 2tb
We are planning to set up the TEMP DB on a seperate disk which is of 300GB and RAID 10 .
As a best practice we are planning to stripe the Data file to 16 files i,e one .mdf and 15 ndf's.
our largest database is around 1TB at present (Data coming in).With huge transaction processing and Batch processes going on.
we are struck up with Defining the optimum sizes for the LOG and Data Files.
Are there any particular statistics to set the Data and log file sizes.Like Log file should be 1/2 the size of Data file.
Tried searching online lot of articles but cannot find any particular statistics.
Any help is appreciated.
Thanks in Advance!!!

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Usually Log file is 20-25% of your datafile. But still it depends on the application you are running, frequency of T-log backup, using logspace at the time of maintenance etc.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

aravind_0404 Say Hey Kid Points: 680 More actions · Answer 2

@free_mascot

Thanks for the reply...The size of log file should be 20-25% of the whole Datafile or the striped ndf's.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

free_mascot (7/15/2009)
Usually Log file is 20-25% of your datafile. But still it depends on the application you are running, frequency of T-log backup, using logspace at the time of maintenance etc.
HTH

Ummmm.... maybe for something else, but not for TempDB. You don't even take backups of TempDB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

aravind_0404 (7/15/2009)
@free_mascot
Thanks for the reply...The size of log file should be 20-25% of the whole Datafile or the striped ndf's.

For TempDB, I'd say "neither". If you're migrating databases as you say you are, you should already have a good idea as to how big these things should be for the given system. Migration shouldn't change those requirements by much.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)