Post reply

Moving TEMPDB

  • April 19, 2013 at 4:27 pm

    #394639

    Hi all,

    I have a default installation and I need to move just the TEMPDB database from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to another partition (E:\) because this database is too big and I don't have enogh space in C:\.

    I did a backup database but I don't know What other considerations should I have

    Using this code:

    use master

    go

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

    go

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

    go

    Thanks,

  • April 19, 2013 at 4:46 pm

    #1608421

    Other considerations besides just moving the files (code should be fine that you provided)

    Consider the size of the files

    Consider more files than just the default 1 data file

    Consider the growth settings for the files

    Monitor for activity and adjust as necessary if contention in tempdb arises.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • April 19, 2013 at 5:03 pm

    #1608429

    ensure the sql service account has full control permissions on the E:\sqldata directory.

    You will need to restart the instance for the change to take effect.

    Delete the old files after successful move.

    You cannot backup tempdb so not sure what you mean by that.

    ---------------------------------------------------------------------

  • April 23, 2013 at 8:22 am

    #1609086

    Thanks for sharing your experiences,

    I will try to apply it.

Viewing 4 posts - 1 through 3 (of 3 total)

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