Safely moving and creating new tempdb files

by Stuart Blackler   Last Updated November 27, 2017 14:06 PM

Two things I'd like to know:

  • how do you safely move tempdb with minimal downtime?
  • how many tempdb files do you need?

Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?



Answers 2


  1. Moving tempdb:

    execute
    ALTER DATABASE tempdb 
    MODIFY FILE ( name=tempdev, filename='D:\Newpath\tempdb.mdf') 
    GO
    ALTER DATABASE tempdb 
    MODIFY FILE ( name=templog, filename='D:\Newpath\templog.ldf') 
    GO
    

    Then restart your SQL Server Service (MSSQLServer).

  2. Number of files in tempdb - see Paul Randall comments: A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

jl01
jl01
July 11, 2012 18:55 PM

To move tempdb files, you simply need to do the following:

alter database tempdb
modify file
(
    name = tempdev,
    filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go

alter database tempdb
modify file
(
    name = templog,
    filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go

If you want to add a new file to tempdb, you simply need to do the following (provided you want to add it to the PRIMARY filegroup, or create your own):

alter database tempdb
add file
(
    name = tempdb2,
    filename = 'C:\YourNewTempdbDir\Tempdb2.ndf'
)
go

For these changes to take effect, you will need to restart the SQL Server service. So as far as minimizing downtime goes, you are constrained to the amount of time it will take for the service restart. You don't have to worry about moving the pre-existing tempdb database files, as SQL Server always recreates the files and the new locations/files will be created upon service startup.

As for the "1 tempdb data file per core", that is largely a myth. The correct approach is to monitor tempdb file contention for the Page Free Space (PFS), Global Allocation Map (GAM), and the Shared Global Allocation Map (SGAM) pages. Please reference this article to get a query that looks through the sys.dm_os_waiting_tasks DMV to see how much tempdb file contention there is. Then you need to go off of this, instead of just blanketing tempdb with the same amount of files as there are cores. It's the more advisable approach.

Thomas Stringer
Thomas Stringer
July 11, 2012 19:39 PM

Related Questions


Unable to remove additional tempdb file

Updated February 07, 2018 02:06 AM

TempDB data files don't shrink well on SQL 2008

Updated April 03, 2018 08:06 AM



Tempdb growing, however it has a lot of free space

Updated January 03, 2018 17:06 PM