Tempdb suggestions - limit size?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Tempdb suggestions - limit size?

Tempdb suggestions - limit size?

Rate Topic

Display Mode

Topic Options

Author

Message

bkDBA

Posted Tuesday, October 14, 2008 9:54 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 13, 2009 9:06 AM
Points: 332, Visits: 158

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

Post #585591

george sibbald

Posted Tuesday, October 14, 2008 2:39 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 5,269, Visits: 11,204

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.

---------------------------------------------------------------------

Post #585800

Matt Miller (#4)

Posted Tuesday, October 14, 2008 4:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

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?

Post #585860

bkDBA

Posted Wednesday, October 15, 2008 6:00 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 13, 2009 9:06 AM
Points: 332, Visits: 158

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

Post #586107

Steve Jones - SSC Editor

Posted Wednesday, October 15, 2008 6:11 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,423, Visits: 13,736

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #586117

« Prev Topic | Next Topic »

Permissions