How to Move tempdb to a Temporary Disk Drive

2 days ago 12

One of the simplest yet most impactful performance optimizations for SQL Server is relocating the tempdb system database to a fast, dedicated disk—especially if that disk is temporary or ephemeral. This is a common practice in cloud environments and high-performance database systems where tempdb churn is high and doesn’t require persistence across reboots. In this […] The post How to Move tempdb to a Temporary Disk Drive appeared first on SQLRx.

One of the simplest yet most impactful performance optimizations for SQL Server is relocating the tempdb system database to a fast, dedicated disk—especially if that disk is temporary or ephemeral. This is a common practice in cloud environments and high-performance database systems where tempdb churn is high and doesn’t require persistence across reboots.

In this post, we’ll walk you through:

Why you should consider moving tempdb
When to use a temporary (ephemeral) drive
How to safely relocate tempdb
Things to watch out for

Why Move tempdb?

The tempdb database is heavily used by SQL Server for:

Sorting operations
Temporary tables and table variables
Row versioning (e.g., for snapshot isolation)
Index rebuilds and hash operations

Since tempdb is recreated every time SQL Server restarts, its contents don’t need to be persisted. If it resides on a slow or busy disk, this can create a performance bottleneck for the entire instance.

Benefits of Moving tempdb:

Faster I/O operations
Reduced contention with user databases
Better performance in temp-heavy workloads
May be required for disaster recovery software to work properly

When to Use a Temporary or Ephemeral Disk

In many cloud platforms (like Azure, AWS, or GCP), virtual machines come with a temporary disk (often labeled D: or /mnt). These disks offer fast, local storage but do not persist after a reboot. This makes them ideal for tempdb, as long as:

Your SQL Server instance is configured to recreate tempdb on boot
You automate or script the directory creation
You monitor free space proactively

How to Move tempdb to a Temporary Disk

Here’s a safe step-by-step guide for moving tempdb to, for example, D:\SQLData\TempDB.

1. Verify Current tempdb File Locations

USE tempdb;
GO
EXEC sp_helpfile;

You should see something like this:

Keep this information handy, just in case things go south.

2. Prepare the New Location

Ensure the target directory (e.g., D:\TempDB) exists. Create it manually or via startup scripts if using a temporary drive. Make sure the SQL startup account has read/write access to the new directory.

3. Run ALTER DATABASE Commands to Move Tempdb

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\TempDB\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'D:\TempDB\templog.ldf');
GO

ALTER DATABASE tempdb2 
MODIFY FILE (NAME = temp2, FILENAME = 'D:\TempDB\temp2.mdf');
GO

ALTER DATABASE tempdb3 
MODIFY FILE (NAME = temp3, FILENAME = 'D:\TempDB\temp3.mdf');
GO

ALTER DATABASE tempdb4 
MODIFY FILE (NAME = temp4, FILENAME = 'D:\TempDB\temp4.mdf');
GO

ALTER DATABASE tempdb5 
MODIFY FILE (NAME = temp5, FILENAME = 'D:\TempDB\temp5.mdf');
GO

ALTER DATABASE tempdb6 
MODIFY FILE (NAME = temp6, FILENAME = 'D:\TempDB\temp6.mdf');
GO

ALTER DATABASE tempdb7 
MODIFY FILE (NAME = temp7, FILENAME = 'D:\TempDB\temp7.mdf');
GO

ALTER DATABASE tempdb8 
MODIFY FILE (NAME = temp8, FILENAME = 'D:\TempDB\temp8.mdf');
GO

If you have multiple data files (common in performance-tuned systems), update them all. This current example has 8 files and a log file.

4. Windows PowerShell Script to Recreate tempdb Folder

Create a PowerShell script that runs at system startup:

# Configuration

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempdbFolder="D:\TEMPDB"

# Make tempdb directory on the SSD

if (!(Test-Path -Path $tempdbFolder)) {
    New-Item -ItemType Directory -Path $tempdbFolder
}

# Start the services

Start-Service $SQLService
Start-Service $SQLAgentService

We saved ours as C:\Startup\CreateTempdbFolder.ps1.

5. Create a Task Scheduler Task to Run the Tempdb Folder Powershell Script

Using Task Scheduler

Open Task Scheduler (taskschd.msc)
Create a new task
- General Tab:
  - Name: CreateTempDBFolder
  - Run with highest privileges
  - Run whether user is logged in or not
- Triggers Tab:
  - Add a new trigger: At startup
- Actions Tab:
  - Action: Start a program
  - Program/script: powershell.exe
  - Add arguments:

-ExecutionPolicy Bypass -File “C:\Startup\CreateTempDBFolder.ps1”

Conditions Tab:
- Uncheck “Start the task only if the computer is on AC power” (for VMs)
Settings Tab:
- Check “Allow task to be run on demand”
- Check “Run task as soon as possible after a scheduled start is missed”

6. Restart SQL Server

Changes won’t take effect until you restart the SQL Server service.

7. Clean Up Old Files

After restarting and confirming tempdb is using the new location, delete the old files.

Tips and Considerations

Startup Scripts: If your temp drive is wiped on reboot, use a script or scheduled task to recreate the tempdb directory before SQL Server starts.
Permissions: Ensure the SQL Server service account has read/write access to the new folder.
Monitoring: Monitor tempdb usage and ensure the temporary disk has enough capacity for worst-case scenarios.

Conclusion

Moving tempdb to a temporary or dedicated disk is a low-risk, high-reward optimization for SQL Server performance. In modern deployments—especially cloud-based environments—this can lead to dramatic performance improvements with minimal effort.

Make sure to automate your directory creation and monitor disk space to keep your setup resilient across reboots. Happy moving!

The post How to Move tempdb to a Temporary Disk Drive appeared first on SQLRx.

View Entire Post

Read Entire Article