How to move SQL 2016 TEMP2 TempDB

Moving your TempDB locations should be performed outside production.

Check Current Temp DB Locations:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO

Move the Temp2 or any additional Temp databases:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = ‘T:\MSSQL\DATA\tempdb_mssql_2.ndf’);
GO

Check Current Temp DB Locations again:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO

Note: you will need to restart the MSSQLSERVICE for your instance for the changes to take effect.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.