Sql-server – Moving TempDB data and log files

sql servertempdb

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?

Best 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:

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.

Related Solutions

Sql-server – SQL Server – Tempdb data/log file placement

If you're already certain you have IO contention, start by understanding which files are the cause.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN 
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

With the data from that query you can judge whether the log or data files are the point of contention in tempdb. It's probable you'd be better off with both data files on one drive and the log file separate, but you'll never know without the numbers to back the decision.

Sql-server – Creating new TempDB files

Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.

I would start with 2 files at most and see if it improves the situation or not. You may even consider leaving one file where it is and adding a second on the new drive (depending on the other activity on the original drive).

Also make sure they are sized exactly the same and they have the same autogrowth setting. As soon as one file is larger than the other, the proportional fill algorithm will only use one file. Consider using trace flag 1117 to make sure that all data files autogrow at the same time, keeping the proportional fill algorithm in check. Just note that this trace flag affects all databases - so if you have other databases with multiple data files, you'll want to make sure their autogrow settings won't cause an issue.

Best Answer

Related Solutions

Sql-server – SQL Server – Tempdb data/log file placement

Sql-server – Creating new TempDB files

Related Question