Moving TempDB data and log files

Posted on

Question :

I had to move our TempDB files around, since both the data and log files were originally on the C: drive.

I moved the data file to our E: drive and the log file to our F: drive.

This worked and I was able to get SQL Server back online, but I noticed that the F: drive, which has the ldf file now, also has another data file.

This made a total of two mdf files, one on the E: drive and the other on the F: drive.

I intended to have just one mdf file and log file seperated on different drives.

Why/how did SQL make the extra file?

Answer :

First verify if what you’re seeing is the same as what SQL Server can see, by running this:

USE tempdb;
EXEC sp_helpfile;

If all 3 of your files are here, you somehow created them there by accident.

TempDB is a ‘special’ system DB that gets re-generated every time you start the SQL Server service.

Setup TempDB the way you want it, restart the server so TempDB recreates itself and run the above query again, to see if the new settings stuck.

If you want to still add a file somewhere manually you can do this:

    NAME = N'LogicalNameHere'
    ,FILENAME = N'D:WhatevertempDB2orWhatever.ndf'
    ,SIZE = xMB

If you want to keep all of your TempDB files but just move them around, get the info from sp_helpdb and modify to include your paths/files/names as found here:

USE [master];

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:Sqldatatempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'E:Sqldatatemplog.ldf');

Note you should usually have multiple TempDB data files. If you don’t, you are risking logical file contention, which is not pretty.

Check out Brent’s blog for more info on SGAM and PFS contention.

Leave a Reply

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