07 December,2012 by Jack Vamvas
There is a requirement to move a TempDB file from one drive to another. There may be many different reasons – such as the Tempb is FULL or it is a scheduled change. Moving a TempDB file requires executing the ALTER DATABASE command and a SQL Server Restart.
View the current location of the TempDB files by :
use tempdb go select fileid,name,[filename] from sys.sysfiles --the output is something like --fileid name filename --1 tempdev G:\MSSQLSERVER\MSSQL$INST1\Data\tempdb.mdf --2 templog F:\MSSQLSERVER\MSSQL$INST1\Data\templog.ldf --3 tempdev1 E:\MSSQLSERVER\MSSQL$INST1\Data\tempdev1.ndf
You’ll notice in the output three files, the “tempdev1” file was added as an extra file earlier on. All I want to complete is moving the”tempdev1” file, but the same principle applies to the tempdev and templog file
USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev1, FILENAME = 'J:\tempdev1.mdf') --then a SQL Server restart