Question

How do I move the tempDB for MSSQL?

 

Answer

Below is a sample script that can be used to move the temp DB and temp DB logs post-installation of MSSQL:

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

 

If there are multiple tempdb and templog files (check the original location), you should run the command for each one.

SQL Server should be restarted for changes to take effect.

 

Overview

Moving the tempDB to a volume with low latency (e.g., SSD, RAID 0, etc.) can dramatically improve database performance.