Home Forums SQL Server 2005 Administering TEMP DB ISSUE RE: TEMP DB ISSUE

  • July 3, 2013 at 3:53 pm

    #1629694

    sqlpanther (7/3/2013)

    hello All ,

    we have issue in our environment where temp db grows very large in 6-8 hrs, the settings are fine and as per standards such as temp db should be set simple recovery, unristrced autogrowth. there is no no open transaction etc. we have tempdb filegroups divided into 7 FG to increase performence and these files increases they dont get truncated automatically, everytime we have option left is to restart the sql server, which i think is not the right choice. during my analysis what I found was we use lots of temp tables in Stored procs, but we also use drop command at the end of SP, is there any other way or anything causing this issue..

    Your help is appreciated ...

    Thanks...

    What do you consider to be "very large"?

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