Need to relocate tempdb

  • 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

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

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

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

  • 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

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

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

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