Post reply

tempdb - Restricted File Growth option

  • February 4, 2009 at 3:14 pm

    #938492

    ahutch

    Sorry my mistake, just checked our scheduled jobs, we don't log ship tempdb ...

  • February 4, 2009 at 3:24 pm

    #938503

    Log shipping doesn't really apply to tempdb, since it is (by default) in simple recovery mode.

    Most things in tempdb give up their disk space as soon as they are done running. No need to try to clear it out or anything like that.

    The place I'd start in a situation like this, where you have limited control over the code, is with a trace. Run a server trace to see what query or queries are running that cause tempdb to autogrow so much. Once you have that data, you'll be in a better position to know what to do about it.

    As far as it goes, moving tempdb to its own set of disk drives (a RAID1 pair is good), is often pretty good for performance for the whole server. But before I'd do that, I'd run the trace and see what's causing it to grow so much in the first place.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 4, 2009 at 3:41 pm

    #938509

    Thanks GSquared,

    Will take aboard your advice with the SQL Trace.

  • February 4, 2009 at 6:25 pm

    #938553

    Colin Lam (2/2/2009)

    Jeff

    How do you go about shrinking the tempdb through the GUI?

    We don't know exactly what is causing the tempdb to grow so big, what we do know alot of the tempdb is just empty space, but it continues to grow, hence the question, why does it not recycle itself ..

    Now, isn't that a bugger? I just tried shrinking it by right clicking on it and follwing my noese the the SHRINK, FILES path. I got it to shrink, but not to what I wanted to and the darned thing allowed me to make entries (in properties) to change the initial size, but they went right back to where they were.

    I'm at sp2 on 2k5... anyone know if sp3 actually fixed this problem?

    --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)

  • February 5, 2009 at 7:51 am

    #938863

    Jeff Moden (2/4/2009)

    Colin Lam (2/2/2009)

    Jeff

    How do you go about shrinking the tempdb through the GUI?

    We don't know exactly what is causing the tempdb to grow so big, what we do know alot of the tempdb is just empty space, but it continues to grow, hence the question, why does it not recycle itself ..

    Now, isn't that a bugger? I just tried shrinking it by right clicking on it and follwing my noese the the SHRINK, FILES path. I got it to shrink, but not to what I wanted to and the darned thing allowed me to make entries (in properties) to change the initial size, but they went right back to where they were.

    I'm at sp2 on 2k5... anyone know if sp3 actually fixed this problem?

    I've had success on SP2 with setting the initial file size for tempdb and then restarting the SQL service and ending up with what I wanted. That was with four files for four CPUs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 16 through 19 (of 19 total)

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