September 10, 2010 at 8:28 am
Hi all
just after some trusty advice 😉
Our tempdb was placed on the wrong partition (c) so i want to move it to to f - that is fine
but i read that i should also create a tempdb for 1/2 the amount of processors i have? is that correct - i have 4
also what size should i set the tempdb to? i have 8gb of ram and i allocate 6gb to sql - so should the tempdb be created as 6gb also?
any tips and tricks to get the best out of sql appreciated
regards
mal
September 10, 2010 at 9:01 am
tempdb get cleared everytime you restart you sql server services.
So by default it get populated with model.
6gb of ram to sql server doesnt mean that tempdb will be of 6 gb. It will be created by default size of model and then will increase depends on how frequently it get accessed for temp transactions.
Number of processor is just for fast processing.
----------
Ashish
September 10, 2010 at 9:36 am
HI ASHISH
YOU SAY Number of processor is just for fast processing. - SO I SHOULD HAVE 2 TEMPDBS THEN?
PS
DRINKING ABOVE THE LIMIT AND DRIVE IS PROHIBITED! AND DOES EVERYONE WHO GOES TO A BAR DRINK - NOT TO MENTION THE STAFF, BANDS ETC - SO I THINK THEY NEED THERE PARKING LOL:-D
September 10, 2010 at 9:43 am
hahahaha............
processing is not related to tempdb only. tempdb is system database
there will be only 1 tempdb like other system database
----------
Ashish
September 10, 2010 at 9:51 am
The best advice I can give you....READ THIS by Paul Randal....:-)
Myth #12: tempdb should always have one data file per processor core.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 10, 2010 at 9:51 am
you say 1 tempdb like other system database
but in this link why is more than 1 recommended
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
September 10, 2010 at 9:54 am
cheers shawn for the link
September 10, 2010 at 10:06 am
there is difference between 'have one data file' and have one database..
Hope you know both terms
----------
Ashish
September 10, 2010 at 10:15 am
yes, i will have 1 tempdb split over 2-4 data files
from reading the link i may not necessarily need to - i guess i need to figure that out myself
September 10, 2010 at 10:23 am
Yes that is the current advice, start with one and monitor the tempdb for i/o issues and if needed add more.
September 10, 2010 at 2:44 pm
There is only 1 tempdb per instance and all the Dbs use it.
However you can create multiple secondary data files for the tempdb and place them on a dedicated Tempdb disk or spread them over multilple disks. Also please note that all these data files should be similar.
But only one log file i.e .ldf
Thank You
Best Regards,
SQLBuddy
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply