Skip to main content
Our Tech Ideas

Change ‘TempDB’ file location in SQL Server

Introduction

The TempDB database is essential to SQL Server’s management of temporary data and execution of different tasks. The TempDB files are often kept on the same disc as the SQL Server installation by default. However, there may be circumstances when you need to relocate the TempDB files to enhance speed or meet storage needs. In this blog article, we’ll examine the procedures for modifying SQL Server’s TempDB’s file location.

Step 1: Identify the Current TempDB File Location

It is crucial to know where the TempDB files are currently located before relocating them. Follow these steps to accomplish this:

  1. Launch SSMS and establish a connection to the SQL Server instance.
  2. Run the following query to find the current location of the TempDB file:
USE tempdb;
GO

SELECT name, physical_name AS 'Current Location'
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

This query will display the current file location(s) of the TempDB database.

Step 2: Prepare the New File Location

When you have chosen the new location for the TempDB files, make sure it satisfies the following criteria:

  • Enough space on the disc to fit the TempDB files.
  • The SQL Server service account has the appropriate security and access rights.
  • A quick and dependable disc subsystem to keep performance at its peak.

To change the TempDB file location, follow these steps:

  1. Detach the TempDB database using the following T-SQL command:
USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (
    NAME = tempdev,
    FILENAME = 'New_Location\tempdb.mdf'
);

Replace 'New_Location\tempdb.mdf' with the appropriate file path and name for the new location.

  1. Move the physical files to the new location using Windows Explorer or any file management tool.
  2. Reattach the TempDB database with the new file location:
USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE (
    NAME = tempdev,
    FILENAME = 'New_Location\tempdb.mdf'
);

Make sure to use the same file path and name that you specified during the detaching step.

Step 4: Restart SQL Server

To apply the changes and start using the new TempDB file location, restart the SQL Server service. You can achieve this through the SQL Server Configuration Manager or the Services console.

Step 5: Verify the New TempDB File Location

After restarting the SQL Server, validate the changes by executing the following query:

USE tempdb;
GO

SELECT name, physical_name AS 'New Location'
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

This query will display the updated file location(s) for the TempDB database.

Conclusion

To maximise speed or satisfy storage needs, it may be essential to change the file location of the TempDB database in SQL Server. The procedures in this blog article will show you how to relocate the TempDB files securely while maintaining the functionality of your SQL Server instance. Always make a backup of your databases before making modifications to important parts like TempDB.