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 the new locations first else the SQL query will give an error.  Cross verify as below that locations get changed.



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

게시 대상: 슈나이더 일렉트릭 Korea