Post reply

TempDB Size not Dropping

  • February 17, 2010 at 4:35 pm

    #386329

    I have a server with tempdb size that shot from 5GB to 10GB in a very short time. I know why the size jump occurred but am confused why it won't go back down.

    I bounced the database services and even restarted the server but tempdb started up immediately using the full 10GB's it previously had been allocated. After the reboot I verified there were no jobs running, nor were there any open transactions. In addition I was the only connection to the server.

    It's in simple recovery and not set to grow via %.

    Ideas? I don't exactly want to brute force it via truncate and shrink.

    Thanks much,

    Michael...

  • February 17, 2010 at 7:40 pm

    #1120615

    plaid_draco (2/17/2010)

    I have a server with tempdb size that shot from 5GB to 10GB in a very short time. I know why the size jump occurred but am confused why it won't go back down.

    I bounced the database services and even restarted the server but tempdb started up immediately using the full 10GB's it previously had been allocated. After the reboot I verified there were no jobs running, nor were there any open transactions. In addition I was the only connection to the server.

    It's in simple recovery and not set to grow via %.

    Ideas? I don't exactly want to brute force it via truncate and shrink.

    Thanks much,

    Michael...

    The only way that I've seen such a thing happen is when someone changed the initial size in properties. If someone did do that, then you may have to shrink it to get it back down to 5GB. I also have to tell you that I'll boot a busy production server with at least 10GB and that someone may have done you a favor by boosting it to 10GB. If you can afford the disk space, I'd simply leave it that way.

    So tell us... what did you isolate the jump in size to?

    --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)
    Intro to Tally Tables and Functions

  • February 18, 2010 at 12:33 am

    #1120694

    Then you have to enable Auto Shrink option in Server.

    But this will impact on server/DB performance.

    Instead of enable Auto Shrink avoid to use temp tables for large amount of data/records.

    Thanks,

    KK

  • February 18, 2010 at 8:51 am

    #1120974

    I assumed after a service bounce tempdb wouldn't immediately consume up to it's maximum allocated size... especially afterward when I wasn't allowing connections other than my own.

    The jump size on that database is 50 megs.

Viewing 4 posts - 1 through 3 (of 3 total)

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