Post reply

How can we reduce the temp db size, if it exceeds its normal size

  • January 7, 2008 at 3:27 am

    #121760

    if temp db exceeds more than its normal size, what are the ways to reduces its size.

  • January 7, 2008 at 8:46 am

    #765378

    How about

    DBCC SHRINKDATABASE ('tempdb')

  • January 8, 2008 at 12:12 am

    #765590

    Hi,

    V thanks for your kind information, at the same what are the possibilities to go tempdb abnormal size.

    In my work place, our prodoction database size is 30GB, initially tembdb size is 3mb, when it was fine. but now it has been reached to 1GB, what is the root cause for this ?

    thanks

  • January 8, 2008 at 12:59 am

    #765604

    hi

    probably there's a new application on your production server that is causing the growth.

    tempdb grows when you create explicitly temporary objects like tables, table variable, sp and cursors

    , when sqlserver creates internal work tables and for some other reasons (please look in BOL)

    in my opinion, the 3mb size of tempdb was very little, probably tempdb was not used at all and 1 GB does not look that much. see how it evolves in time.

    you should also take care that if you shrink it and it needs to grow again, that might cause a bit of extra work for your server

  • January 8, 2008 at 6:45 am

    #765695

    If you have TempDB set up to auto-grow by 10% like everyone else seems to, it will cause 73 fragments as it grows to 1 gig. And, I agree with dragos_sv... 3MB is small... way too small... virtually every operation you do will have something to do with TempDb... it's the system's scratchpad and the system will stop and wait anytime growth is needed. Set it to 1 or 2 gig to begin with. Our production database boots up with a planned 12 gig TempDB so, in comparison, your 1 or 2 gigs is almost nothing.

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

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

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