October 14, 2008 at 9:54 am
I'm interested to know people's opinions on a tempdb setting. What are the pros/cons of setting tempdb to a maximum file size in SQL Server 2000? Should tempdb be set to "unlimited growth"?
Can anyone give a good reason to force a limit on tempdb size (provided there is ample diskspace available)?
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
October 14, 2008 at 2:39 pm
if there is ample diskspace I can think of no reason to limit the size of tempdb. IF tempdb fills everything comes to a halt till its cleared so why risk it? You would have to be very confident of the maximum size your tempdb will ever reach.
If you have split tempdb into multiple files across drives maybe then there is a case for an individual file if its on a space limited drive.
What you should do is ensure the tempdb is set to the size it needs to be during normal operations to save wasteful tempdb file growths which will hit performance.
---------------------------------------------------------------------
October 14, 2008 at 4:13 pm
I'd go one step further - you probably want to make TempDB plenty big (both on data and log files), so that you can avoid having to contend with auto-growth. There's no real justification to make tempDB have to grow in small increments, especially if there is a relatively stable size it stays at after the server is on for a while.
And yes - split the file up some if you can (even if it's on the same drive, although it would be best if they were spread across drive groups.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2008 at 6:00 am
Thanks... so far these suggestions match my thoughts pretty well. In general, and especially on smaller systems, I leave tempdb alone. I don't set a max size limit on tempdb, and monitor it instead. I've had a "consultant" however, insist on limiting the max size of tempdb at a rather small size. In my opinion this is planning for failure, but I wanted to check to see if anyone had a good argument in favor of a max size limit on tempdb.
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
October 15, 2008 at 6:11 am
The reason I might set a limit is to prevent someone from running something that makes heavy use of tempdb, and perhaps tracking it down. It's a fairly heavy handed approach, but it might make sense if you have people trying to "test" on your server and don't want large cross joined sorts or worktables built.
Or if you have some need to keep space available on that drive.
In general I agree with the advice and I'm guessing your consultant just doesn't monitor growths or doesn't care to. Or doesn't know to.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply