1

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?

2

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:

ALTER DATABASE tempdb ADD FILE (
    NAME = N'LogicalNameHere'
    ,FILENAME = N'D:\Whatever\tempDB2orWhatever.ndf'
    ,SIZE = xMB
    ,FILEGROWTH = 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];
GO

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'E:\Sqldata\templog.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.

  • Thank You Ali. I ran sp_helpfile and I can see that both TempDB data and log file are on they drives as expected and even after restarting the SQL Server they structure remains same but how can the second data file which still exists alongside the drive specified for log file be explained? does it mean tempDB will utilize both MDF files? – user48070 Jun 19 '15 at 20:38
  • Negative, TempDB will only utilize what is in sp_helpdb. Now the mystery of the 2nd file. I suspect it's a orphaned file left over from a prior test or DB. Sometimes removing the DB from SQL Server doesn't remove the data files I've noticed. If you don't have other SQL instances running (get-service sql from powershell), you can try moving that file elsewhere then deleting it later. – Ali Razeghi Jun 19 '15 at 21:44
  • Anytime bud, let us know if you have any more questions later on. If you feel this answer fully answered your question, give it a check stating answer so other's can see it worked for you and I get magic points. :P – Ali Razeghi Jun 19 '15 at 23:17
  • 1
    On SQL Server 2017 RC1, there are not just 2 files for tempdb, there are 10. The 10 files are the original 2, plus 8 numbered files. You will have to first check which files it is using, then add 10 "ALTER DATABASE ..." commands to shift everything to other drive(s). – Contango Oct 13 '17 at 7:54
  • Thank you for the comment and updating the answer to 2017 @Contango! – Ali Razeghi Oct 14 '17 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.