Post reply

Shifting TEMPDB database to another folder in same server.

  • December 21, 2008 at 4:14 am

    #199169

    Dear All,

    I want to change the location of my tempDB data base.

    How can I dot it..? I also want to do it without stopping SQL Server..

    Is it possible..? Please guide.

    Thanks in advance.

    Santhosh.

  • December 21, 2008 at 4:50 am

    #915817

    You can do it by running alter database statement. You can look at the script .that I’ve added as an example. Unfortunately you'll have to restart the service. Without restarting the service, tempdb will continue to work from its current position.

    use master

    go

    --Change the location of the data file. .

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

    Go

    --Change the location of the log file.

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

    go

    Don't forget to use the real path and not the path in this example.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 21, 2008 at 4:57 am

    #915818

    Thanks...will try the same.

  • December 21, 2008 at 5:04 am

    #915821

    Use ALter database with Modify file option to move the tempdb database and do an instance restart.

    FOllowing links would be helpful.

    http://www.sqlservercentral.com/articles/Administration/63568/

    http://support.microsoft.com/kb/187824

    or in BOL

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72bb62ee-9602-4f71-be51-c466c1670878.htm


    Pradeep Singh

  • December 21, 2008 at 5:07 pm

    #915915

    I post this for the benefit of others who may search the forums: PLEASE check Books Online before posting questions! It is a WONDERFUL resource! I put 'tempdb' in the index and voila, there was a clickable link 'moving', which took me directly to everything you need to know about moving the tempdb. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 21, 2008 at 5:15 pm

    #915916

    Nah... too much work, Kevin. 😉

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

  • December 22, 2008 at 12:43 am

    #915980

    TheSQLGuru (12/21/2008)

    PLEASE check Books Online before posting questions!

    Read the manual? What a radical, way out idea!

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2008 at 8:37 am

    #916188

    Well, some manuals (especially these days) aren't very good. But the 'softies really did a nice job with SQL Server's BOL! Most people don't know that. I have it open 24/7 on my laptop.

    I sure do miss the old days though, where you could get nice hard-copy manuals though. Gosh - that sounds SOOOO old school! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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