Home Forums SQL Server 2005 Administering Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB? RE: Current tempDB size at 2 GB. Anyway I can create a new tempDB to start out at 100 MB?

  • September 28, 2012 at 8:05 am

    #1543434

    Bhuvnesh (9/28/2012)

    Jeff Moden (9/28/2012)So if you want a 12GB TempDB, are you suggesting to set the otherwise unused Model database to 12GB? Doesn't sound right to me. It's better to properly size TempDB in properties.

    jeff, i didnt get your point here , what i suggest is , set the model at 100MB so that tempdb initial size would be 100 ma at restart.

    Model is already set pretty small so that actually might not do any good. You need to set the intial size of TempDB through the properties of the TempDB database.

    The other problem here is that if you set it to 100MB and you have processes that need it to be 2GB, it's a futile act that will also cause delays in the process while TempDB grows to meet the demand.

    Also, the default settings for TempDB growth are terrible and will cause much fragmentation. The growth properties need to be changed anyway.

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