move SQL Server TempDB database files to a different directory using Dynamic SQL

This article will explain the steps to change the SQL Server Tempdb database data and log file physical location.

The TempDB database is created during the SQL Server installation process and is used by SQL Server to store internal objects, such as transient tables, internal queries, and user-defined variables.

The default location :

C:\Program Files\Microsoft SQL Server\MSSQLVersionNumber>.MSSQLSERVER\MSSQL\DATA.

However, you may want to change the location of the Tempdb database data and the Tempdb database log file to different locations.

Check Existing TempDB database files location

First, We need to check the current location of Tempdb files as it is essential to clean orphan(old) Tempdb files that are left once we move the Tempdb to a different location.

Select db_name(dbid) as 'Database_Name',name as 'Logical_File_Name',
Filename as 'Physical_Location' from sysaltfiles
Where db_name(dbid)='Tempdb'

Or you can use the sp_helpfile stored procedure to verify the current TempDB files.

use TempDB
Go
Exec sp_helpfile

You can verify the TempDB files in the specified directory.

Move TempDB files to a different directory

Suppose the requirement is to move TempDB files from the existing (C drive) location to the new directory- D:\data\mssql

Run below Alter statement to alter both data and log files.

Use master;
GO
Alter Database TempDB
Modify File (Name=temdev, Filename = 'D:\data\mssql\tempdb.mdf');
GO
Alter Database TempDB
Modify File (Name=templog, Filename = 'D:\data\mssql\templog.ldf')
GO

Note: If you have multiple TempDB data or log files, you need to run the ALTER DATABASE statement for each TempDB file you want to move into the new directory.

After running the Alter statement specified above, you need to restart the SQL Server service. Once you restart SQL Server, create new TempDB files into the new specified directory.

To restart SQL Server, launch SQL Server Configuration Manager from the Start menu and restart SQL Server service as shown below.

Once SQL Service is restarted, you can connect to the SQL Server instance and verify the TempDB directory. As shown below, my TempDB files are now in the directory D:\data\mssql.

You can browse the TempDB directory and verify the existence of the TempDB file in the directory.

Note: Always remember to drop old location files. Otherwise, it acts as an orphan file and keeps consuming space.

Dynamic SQL to generate the ALTER DATABASE statement for TempDB files movement to the new directory

This section uses dynamic SQL to generate the ALTER DATABASE statement. It requires the input for the destination (new) directory for TempDB files, and you can run the ALTER DATABASE statement from the query output.

/*
******************************************************************
Specify the new directory name in the variable @NewTempDBDir 
******************************************************************
*/
DECLARE @NewTempDBDir VARCHAR(1000)
SET @NewTempDBDir='D:\MSSQL\Data\'  
 DECLARE @TempDBFiles TABLE
 (
    id                      INT IDENTITY(1, 1) PRIMARY KEY,
    [file_id]               INT,
    [name]                  VARCHAR(20),
    [physical_name]         VARCHAR(1000),
    [extract_physical_name] VARCHAR(100) NULL,
    sqlscript               VARCHAR(max) NULL
 )

INSERT INTO @TempDBFiles
            ([file_id],
             [name],
             [physical_name])
SELECT file_id,
       NAME,
       physical_name
FROM   tempdb.sys.database_files

--Select * from @TempDBFiles

DECLARE @i INT
DECLARE @String NVARCHAR(max),
        @SQL   NVARCHAR(max)

SELECT @i = Count(*)
FROM   @TempDBFiles

WHILE ( @i > 0 )
 BEGIN
     SELECT @String = physical_name
     FROM  @TempDBFiles
     WHERE file_id = @i

     UPDATE @TempDBFiles
     SET   [extract_physical_name] = 
     Reverse(Substring(Reverse(@String), 0,                     Charindex('\', Reverse(@String),0)))
     WHERE file_id = @i

     SELECT @SQL = 'ALTER DATABASE [Tempdb] MODIFY FILE ( NAME='
                    + NAME + ', FILENAME= ' + @NewTempDBDir
                    + extract_physical_name + ')'
     FROM  @TempDBFiles
     WHERE file_id = @i

     UPDATE @TempDBFiles
     SET   sqlscript = @SQL
     WHERE file_id = @i

     SET @i=@i - 1
 END

SELECT name, extract_physical_name, sqlscript
FROM  @TempDBFiles  

Let’s add a new TempDB secondary data file for the testing script.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdev1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

The dynamic SQL script generates the TempDB data file move script for all existing data and logs files.

Buy Me a Coffee if I helped you.