Post reply

Need to relocate tempdb

  • June 18, 2008 at 10:28 am

    #126082

    Hi,

    I need to relocate tempdb to another drive. Obviously. sp_detach_db does not work. Is there a way to do it without stopping the server ?

    In case if I will have to stop the server anyway, how it wil recognize new tempdb location at the startup ?

    Thanks

  • June 18, 2008 at 10:46 am

    #830628

    Try This:

    USE MASTER

    Alter database tempdb modify file (name = tempdev, filename = 'E:\NEWFOLDER\tempdb.mdf')

    go

    Alter database tempdb modify file (name = templog, filename = 'F:\LOGFOLDER\templog.ldf')

    Maninder
    www.dbanation.com

  • June 18, 2008 at 11:31 am

    #830656

    I am not aware of any way to do it other than restarting the SQL Service. BOL has a topic which specifically addresses moving system databases and includes an example for how to move tempdb.

    Link: http://msdn.microsoft.com/en-us/library/ms345408.aspx

    One note - if you plan on resizing tempdb do it after you move to the new drive; if you do it before the move (or as part of the same command) SQL will try to do the resize operation. If you don't have enough disk space on the disk where it currently sits you'll get an error.

    Kendal Van Dyke
    http://kendalvandyke.blogspot.com/[/url]

  • June 18, 2008 at 11:51 am

    #830667

    Use the command Mani given and once done restart sql services.. then delete the old files from the old directory...

  • June 18, 2008 at 1:29 pm

    #830725

    my favorite method is the following

    create new tempdb files on the drives you want them on

    restart SQL

    shrink the default tempdb files if they are large

    set growth restrictions on them of a few MB

  • June 18, 2008 at 2:28 pm

    #830759

    The link that Kendal referred to has a full example of exactly how to do this along with certain explanations as to why you need to do (or not do) certain things. Even though most of the post on this thread are correct, it's worth reading the link... it's worth learning the "why" along with the "how". 😉

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

  • June 18, 2008 at 2:58 pm

    #830788

    Hi,

    Why move tempdb?

    http://www.sqlteam.com/article/moving-the-tempdb-database

    Working with tempdb in SQL Server 2005

    http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

    Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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