Every time I have to do this, I Google for a script. I might as well write my own and put it here so at least I find myself in the Google results:
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + 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');
I can then copy/paste the results into SSMS, edit them, and run ’em. I like generating scripts like this rather than flat-out executing it automatically because sometimes I need to tweak specific file locations.
I’m not bothering with detecting what the file names were before, and I’m just generating new file names to match the database object’s name instead.
Erik says: See that path? Make sure you change that path to the right one. Preferably one that exists, and that SQL Server has permissions to. If you don’t, your server won’t start up unless you Google “how to start SQL Server without tempdb” and spend the next morning explaining to your boss why you should keep your job. I mean, I’ve heard stories…
Want to learn more? Take my class.
My Fundamentals of TempDB class is for curious folks who want to learn:
- What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
- How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
- How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts