21

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?

22

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 (alternative link) 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.

8
  1. To move 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's article: A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

4

From Microsoft's advice:

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors.

If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Moving the TempDB files is a 2-step process:

  1. Tell SQL where you want your new TempDB files to go to (this doesn't have downtime)
  2. Restart the SQL Server service for the change to take effect (this is the minimum downtime you need)

To tell SQL where to create the new TempDB files, you can use:

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size / 128 AS [Size(MB)] --Number of 8KB pages / 128 = MB
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

This will generate the T-SQL statements you need to run to move the files to the new drive:\folder you want. (click image to make larger)

Image showing 2 rows with details on TempDB files and T-SQL statements to move them

When you have ran your moving statements, you can run the above query again, to check that the Current Location column is now showing your new drive:\folder.

Image showing TempDB files' new locations

Once you're happy with your changes, restart the SQL Server service.

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.