Working with one of my education customers I recently had to perform some maintenance on their SQL Database server as they were running low on disk space AND had a free unused virtual disk where we could throw their tempdb onto (it was meant to go here but they didn’t place it there during installation). So I had the task of moving it over.
First step is to get an as-is of where tempdb currently is and how many fragments there are; Open up SQL Management Studio and run the following query.
-- Lists all current tempdb files and their paths SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID (N'tempdb'); GO
Now that we have a listing (similar to our screenshot above/left), we then need to build up a query to move the database files from one drive to another. It should look something similar to the below query, changing paths and adding/removing tempdb fragments as needed (simply add more alter database statements for each file node).
USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, FILENAME = 'F:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf'); GO
In the above example we have specified F drive. The other thing to note is that if you have SQL Server running through a service account, that will need NTFS modify permissions to the destination. Once that is executed under the Master database context the files will then be re-created on the next SQL Server service restart.