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

Updated: Jan 5

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'

View existing directory for the TempDb

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

use TempDB
Exec sp_helpfile

sp_helpfile output

You can verify the TempDB files in the specified directory.

View TempDB files in the 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;
Alter Database TempDB
Modify File (Name=temdev, Filename = 'D:\data\mssql\tempdb.mdf');
Alter Database TempDB
Modify File (Name=templog, Filename = 'D:\data\mssql\templog.ldf')

Alter Database statement for TempDB move

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.

 SQL Server Configuration Manager

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.

Verify SQL ServerTempDB directory

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

TempDB directory

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

Drop OLD TempDB files

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 
SET @NewTempDBDir='D:\MSSQL\Data\'  
    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

SELECT file_id,
FROM   tempdb.sys.database_files

--Select * from @TempDBFiles

        @SQL   NVARCHAR(max)

SELECT @i = Count(*)
FROM   @TempDBFiles

WHILE ( @i > 0 )
     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

                    + 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

SELECT name, extract_physical_name, sqlscript
FROM  @TempDBFiles  

Dynamic SQL to generate the ALTER DATABASE statement

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

USE [master]
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 )

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

dynamic SQL script generates the TempDB data file move

33 views0 comments