April 6, 2005 at 9:27 am
Hi,
Can anybody suggest the tempdb size settings. If I have a tempdb datafile size of 1GB, what would be the ideal size for templog file.
Phani
April 7, 2005 at 7:54 am
The use of Tempdb depends on a lot of things: how heavely cursor, sorts, on-the-fly agregations are used, rollups, etc. Also Tempdb is shared with all DB's and users, so as usual, it depends.
IMHO this what you can do: set the log file to 500 MB, and set both to autogrow with 100 MB increments. Then let it run for a week or so and then use the database's Taskpad view in Enterprise Mgr. to find out how filled both files are. And make adjustments from there.
Word of caution: every time you shutdown SQL, Tempdb gets deleted. And we you start it up again, its created empty. So make sure to check its size before you restart SQL.
Hope this helps.
April 7, 2005 at 12:19 pm
Here's what we have done at our site as a default starting point (19+ instances and 200+ databases) ... we are 95% prodution ...
tempdb data
1024 Mb initial allocation
growth in 512 Mb
growth limit 2049 Mb (to get the second growth of 512 Mb)
tempdb log
512 Mb initial allocation
growth in 256Mb
growth limit 1025 Mb (to get the second growth of 256 Mb)
There's only 1 server where it's larger (double everything).
Of course in order to get there we had to firat make a guess and then monitor like crazy so as not to interrupt our 24x7 systems.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply