I ran into a disk space situation on a new database server and decided to move the Tempdb files to a new drive location. My first thought was to detach the database and move the files.
It turns out you can’t detach a tempdb database since it’s a system database. The following T-SQL query helped achieve the task.
Determine the Logical Names of the TempDB Data and Log files:
Login to SQL2012 Management Studio.
Expand the Databases tree and expand the system databases tree.
Select and right-click on the TempDB database.
Select Properties and on the properties window, note the tempdb files logical names as indicated below:
Use the following script to move the files Edit the file name parameter values to reflect the destination path :
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'E:\MSSQLData\Tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'F:\LogFiles\Tempdb.ldf');
GO
Restart the SQL Instance after the move.
I verified that the file path was changed . I finished by removing the old temp files from the previous location.