Technical FAQs

Ask a Question

How to change the location of database TempDB files Tempdb.mdf and TempLog.ldf in SQL 2008 and 2012 from default location ?

With use of SQL Server the size of database TempDB grows and logical drive C may be short of space. Default location is as shown in snapshot.



To change the location take following steps.
Determine the  file names of the tempdb database and their current location on the disk.  Files used by the tempDB database is TempDB.mdf and Templog.ldf. 
Use below SQL query to check the same. 
SELECT name, physical_name AS Current Location 
FROM sys.master_files 
WHERE database_id = DB_ID(N'tempdb'); 
GO 

Now use below SQL query in SQL server management studio to change the location of  file by using ALTER DATABASE as shown.

USE master; 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'G:\SQLData\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'G:\SQLLog\templog.ldf'); 
GO 



Make sure to create folders for new location first else SQL query will give error.  Cross veryify as below that locations gets changed.



Delete Tempdb.mdf and Templog.ldf files  from old location.


 
Was this helpful?
What can we do to improve the information ?