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?

  • 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.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs