Quick way to change tempdb files location. This is very useful script copied from Brent Ozar. From time to time there is need to move tempdb files to new location. For example after not optimal installation where all SQL Server data files are on the same disk drive. With this script you will quickly generate moving script which can be executed afterwards. And keep in mind this operation requires restart of SQL Server.
TIP: When executing in SSMS change results output to text using Ctrl+T or using GUI. Then you can just copy-paste the result to new query.
Switch result to text in Management Studio
Script source: https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''[your:\path\here]' + f.name + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END + ''');' FROM sys.master_files f WHERE f.database_id = DB_ID(N'tempdb');
Results
Script messages after execution:
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = '[your:\path\here]tempdev.mdf'); ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = '[your:\path\here]templog.ldf'); ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = '[your:\path\here]temp2.mdf'); ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = '[your:\path\here]temp3.mdf'); ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = '[your:\path\here]temp4.mdf');