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.
