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 30, 2012 at 10:00 am

    #1543745

    Jeffrey Williams 3188 (9/29/2012)

    Wow - so much mis-information...

    Tempdb will be resized to its initial size upon restart. If it has grown to 2GB over time - then that is the size it needs to be. If the initial size was set to 2GB, then changing the model database, shrinking the database, or any other options are not going to change this.

    To change the initial size, you have to put SQL Server into single-user mode. Then, you can resize the files using SHRINKFILE with a new size. Once that is done, the initial size will be reset and when you restart SQL Server it will resize tempdb back to the new initial size.

    Note: shrinking tempdb while users are accessing the system could cause corruption in your databases. It is not recommended and should not be attempted unless you can insure that no users are accessing it. That is why you need to put SQL Server into single-user mode.

    +1. And, you should still change the awful default growth settings to something more reasonable.

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