7

This question already has an answer here:

How do I move my TempDB Data or Log File(s) from wherever they are now to a different Drive or Folder?

marked as duplicate by LowlyDBA, RDFozz, mustaccio, Tom V, McNets Mar 27 '18 at 8:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

8

Moving the TempDB files is a 2-step process:

  1. Tell SQL where you want your new TempDB files to go to
  2. Restart the SQL Server service for the change to take effect

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.

3

The calculation of the size(MB) in the script above is off. Don't forget that size in sys.master_files is the number of 8KB pages. Here is the correct script:

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size*8/1024 AS [Size(MB)] --Number of 8KB pages dived by 1024
    ,'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];
  • Thanks for pointing that one out! I usually remember the 8K page thing, but I must have not had my morning tea that day... ^_^ – Oreo Mar 27 '18 at 10:36

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