Moving the SQL Server tempdb file location after SQL Server is installed


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.


Leave a Reply

More Posts

Using Stunnel to Allow Legacy Apps and Devices that do not support SSL POP3 or TLS SMTP to Connect to Office 365

I’ve been busy lately assisting with a number of Office 365 migrations. Every single one is different and while many are straightforward, In some cases, you will find applications or devices that don’t support the requirements for connecting to Office 365 using TLS or SSL or they may not even work over standard ports such […]

How to log into ASDM for a Cisco ASA when you get the Unable to launch device manager error

I like to keep my client’s devices up to date to prevent any issues from arising and have access to the devices latest features. Unfortunately not everyone is happy to pay that little extra to have an on-going support / maintenance agreement.  I was recently out at a customer who had an ASA 5505 which […]

Thoroughly cleaning up a WSUS server

I was recently tasked with performing a clean-up of some of our servers, removing old files/software installations as well as a clean-up of our WSUS server.  After a quick look I could see that our previous administrator had set it to download Driver updates as well, which was taking up quite a large amount of […]