Moving the tempdb database

Posted: January 29, 2009 in Sqlserver

You can move tempdb files by using the ALTER DATABASE statement.

1.    Determine the logical file names for the tempdb database by using sp_helpfile as follows:

   use tempdb



The logical name of the database file can be found in name column.                                                            

Ex: tempdev (Will be the name master database file .mdf) and templog (Will be the name of  Transaction log file .ldf) .

2.    Use the ALTER DATABASE statement, specifying the logical file name as follows:

   use master


  Alter database tempdb modify file (name = tempdev, filename = ‘E:\Mssql\tempdb.mdf’)


  Alter database tempdb modify file (name = templog, filename = ‘E:\Mssql\templog.ldf’)


3.    Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.


  1. Michael says:

    Thanks, that fixed the problem I was having. Reports would fail as there was not enough room on the C drive to grow the tempdb enough.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s