Moving SQL Server tempdb Files to a Different Location in SQL 2016

SQL Server performance improves if data, logs, tempDB data and temDB logs resides in separate drives. If data and logs are in same drive, after a certain period size of database TempDB grows and logical drive C may be short of space. In this example, default location is as shown in snapshot.

How to identify current location?

stmt:

select name, physical_name as currentlocation from sys.master_files where database_id = DB_ID(N'tempdb');

Results:

tempdev       S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb.mdf

templog         L:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\templog.ldf

temp2 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_2.ndf

temp3 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_3.ndf

temp4 S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA\tempdb_mssql_4.ndf

..as seen using SQL (console) management studio

So, based on best practice let us move the tempdb data and logs from S drive and L drive to T and M drive respectively.

(please note that drive letters can vary, here in my environment I have used these letter for mapping drives)

As we have seen existing Path of tempdb data is at S:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\DATA  and existing Path of tempdb log is at L:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data

Let us move tempdb data to T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data  and tempdb log to M:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data

Statement:

Use master;

Alter database tempdb modify file (name = tempdev, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb.mdf');

Alter database tempdb modify file (name = templog, filename = 'M:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\templog.ldf');

Alter database tempdb modify file (name = temp2, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_2.ndf');

Alter database tempdb modify file (name = temp3, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_3.ndf');

Alter database tempdb modify file (name = temp4, filename = 'T:\Program Files\Microsoft SQL Server\MSSQL13.PINTRAAPPS\MSSQL\Data\tempdb_mssql_4.ndf');

Results:

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp2" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp3" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "temp4" has been modified in the system catalog. The new path will be used the next time the database is started.

Now Restart SQL server services for PINTRAAPPS instance

It is always good to delete orphaned data, so Delete Tempdb.mdf and Templog.ldf files  from old location (S & L drive).

I hope the above makes sense.