A common task for a DBA is to move tempdb files to a seperate drive. During a SQL Server installation, the tempdb files (.mdf and .ldf) are stored in the same drive the sql binaries are installed along with master, msdb and model files. As a best practice, tempdb files should always be moved to a seperate drive. The following provides the steps involved in moving tempdb files:
1) Get the logical and physical name for tempdb files.
SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name
2) Modify the “NAME” adn “FILENAME” with the new value for tempdb
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdb, FILENAME = ‘T:\SQLDATA\tempdb.mdf’ )
GO
3) Validate changes took place.
SELECT d.name
, mf.name logical_name
, mf.physical_name
, mf.type_desc
FROM sys.master_files mf
INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.name = ‘tempdb’
ORDER BY mf.type, mf.name
4) Restart the sql server service
After SQL Server is restarted, the new files will be created in the new location. The old tempdb files will remain in previous location and they will need to be deleted manually.