SQL Server - Move TempDB files

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

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');

Leave a Reply

Your email address will not be published. Required fields are marked *