none
Change location of tempdb files RRS feed

  • Question

  • Hi,
    I have a database Server (SQL Server 2005) with user and system databases.

    The location of my tempdb database files tempdev and templog is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data which is the default location.

    Due to the large filseize of tempdb.mdf, i want to change the location of this file to another drive (I)

    How do i accomplish this? Is there a procedure for this? Does it affect other databases?

    Please advice

    Sunday, December 6, 2009 6:12 AM

Answers

  • Hi Rubsay,

    Tempdb can be moved to different drive without much trouble.

    Just run the below command
    sp_helpfile

    Get the logical filenames for tempdb and use it in NAME parameter in the below query.
    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

    E:\SQLDATA has to replaced with repective drive name and folder names.

    You will get an output that system catalog is updated.
    Restart SQL SERVER.
    Now your tempdb will be started from the new drive location and you may delete the old files.

    Thanks, Leks
    • Proposed as answer by Kalman TothModerator Sunday, December 6, 2009 6:43 AM
    • Marked as answer by RubSay Monday, December 7, 2009 6:43 AM
    Sunday, December 6, 2009 6:26 AM
    Answerer
  • Hi RubSay,
    There is a step-by-step example of doing this in the product documentation (aka Books Online).  See the Example in this topic: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx.

    Kind regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    • Proposed as answer by Kalman TothModerator Sunday, December 6, 2009 10:57 PM
    • Marked as answer by RubSay Monday, December 7, 2009 6:43 AM
    Sunday, December 6, 2009 5:20 PM
  • Ok....my bad... I had to wait a little more longer after the Restart step...!
    • Marked as answer by LekssEditor Monday, December 7, 2009 1:56 PM
    Monday, December 7, 2009 10:55 AM

All replies

  • Hi Rubsay,

    Tempdb can be moved to different drive without much trouble.

    Just run the below command
    sp_helpfile

    Get the logical filenames for tempdb and use it in NAME parameter in the below query.
    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

    E:\SQLDATA has to replaced with repective drive name and folder names.

    You will get an output that system catalog is updated.
    Restart SQL SERVER.
    Now your tempdb will be started from the new drive location and you may delete the old files.

    Thanks, Leks
    • Proposed as answer by Kalman TothModerator Sunday, December 6, 2009 6:43 AM
    • Marked as answer by RubSay Monday, December 7, 2009 6:43 AM
    Sunday, December 6, 2009 6:26 AM
    Answerer
  • Hi Lekss. Thanks for the response.

    Do i need to replace E:\SQLData with the new path (drive\folder) where i want to save the files? Or r u referring it as the existing location of these files?
    Sunday, December 6, 2009 9:10 AM
  • Yes absolutely.
    You would have to replace E:\SQLDATA in the script with the new drive and path where you wanted to have your tempdb.
    Thanks, Leks
    Sunday, December 6, 2009 9:28 AM
    Answerer
  • Hi RubSay,
    There is a step-by-step example of doing this in the product documentation (aka Books Online).  See the Example in this topic: http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx.

    Kind regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    • Proposed as answer by Kalman TothModerator Sunday, December 6, 2009 10:57 PM
    • Marked as answer by RubSay Monday, December 7, 2009 6:43 AM
    Sunday, December 6, 2009 5:20 PM
  • Hi..

    After i follwed the steps as in the link...i am getting below error message when i try to connect to Mgt studio on that server. Please advice

    ===================================

    Cannot connect to servername.

    ===================================

    Login failed for user ''. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

    ------------------------------
    Server Name: sdohhqdev02
    Error Number: 18452
    Severity: 14
    State: 1
    Line Number: 65536


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    Monday, December 7, 2009 10:33 AM
  • Ok....my bad... I had to wait a little more longer after the Restart step...!
    • Marked as answer by LekssEditor Monday, December 7, 2009 1:56 PM
    Monday, December 7, 2009 10:55 AM