How To Move TempDB on SQL Server

Publish on: 2020-01-28 Classify at: SQL Server ;

This post is going to walk through the quick steps to move TempDB through TSQL and the Windows Server UI. This information will be useful you are looking to move TempDB away from your user databases and/or away from the OS. Beyond the potential for an interruption during the addition of a drive to the OS, moving TempDB requires the SQL Server service to be restarted. Such a minor interruption makes the potential benefit of this change worthwhile. While this post isn’t going to dig into any of the details around physical disk configuration for performance optimization or availability, it is part of the foundation to improving a SQL Server from a scenario where all the components are stored on 1 drive.

Why?

The TempDB system database is a global resource that is available to all users connected to the instance of SQL Server or connected to SQL Database.
Microsoft, https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

A lot like the communal kitchen at an office where old Tupperware and suspect mason jars go to rot, TempDB gets to keep a lot of the less desirable things in SQL Server.

temporary stuff (that you’ll likely never need again)
- temporary tables, indexes, procedures
- table-valued function return sets
- tables returned by cursors
the database engine’s internal intermediate results (that SQL will never need again)
- occasional queries with ORDER BY, GROUP BY, and UNION
- hash operations
row versions for certain version store operations

While the things temporarily stored in TempDB aren’t necessarily bad, it does make TempDB subject to sudden growth due to one or a handful of queries. In this case, the recommendation is that the TempDB files are stored on their own drive to protect the OS and other files from sudden interruption related to issues with TempDB files.

Move TempDB

Not only can the size of TempDB files be unpredictable (unless the workload is completely predictable or a size limit is placed), but it is full of old Tupperware. That is, if TempDB is destroyed, your SQL Server will create a new one as soon as the service restarts. The whole migration is summarized in these 4 steps:

Create a new location for TempDB
Use TSQL to change the TempDB file location(s)
Restart the SQL Server service during a maintenance window
Verify and clean up

1. TempDB’s New Location

Adding a logical disk to the operating system should not cause a service interruption, but depending on your organization’s structure it may require mountains of paperwork or weeks of waiting.

While we are focusing on moving TempDB for stability, there are additional considerations for TempDB incurring quite a bit of input/output. If you are experiencing performance issues related to TempDB (hint: the things it stores), make sure you check out the resources at the bottom of this post to put your new logical disk on the optimal physical drives.

It is critical that the new location (folder) has been granted permission for SQL Server. In the Windows Explorer security dialog for the location, the desired object principal is “NT SERVICE\MSSQLSERVER” and you will check the box for allowing full control. (named instance of SQL Server? NT SERVICE\MSSQL)

2. T-SQL To Update TempDB’s Location

While your instance is live we can query the server for the current location(s) and update the intended file locations – our changes won’t take effect until the service is restarted.

While connected to master:

SELECT name, physical_name, state_desc
    , size, max_size, growth, is_percent_growth
FROM sys.master_files  
WHERE database_id = DB_ID(N'tempdb');

How many data files do you have for TempDB? How many data files should you have for TempDB? The tl;dr for TempDB files is 1:1 with logical processors up to 8, then as needed from there. How many processors does your SQL Server have?

SELECT cpu_count
FROM sys.dm_os_sys_info

That communal kitchen is more efficient with multiple fridges for that abandoned Tupperware. Consider adding additional TempDB files if appropriate at this time.

If you are modifying the existing TempDB files, you would use similar TSQL to this:

ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'G:\tempdb\tempdb.mdf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'G:\tempdb\templog.ldf');  
GO

If you are also adding TempDB files, you would use similar TSQL to the below to add a single data/log file pair:

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2'
    , FILENAME = N'G:\tempdb\tempdb2.mdf'
    , SIZE = 1024MB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'templog2'
    , FILENAME = N'G:\tempdb\templog2.ldf'
    , SIZE = 64MB , FILEGROWTH = 10%)
GO

It is ideal for the initial sizing to be equivalent to the size returned of the current single TempDB file to avoid lopsided allocation from the start. It is even more ideal for all of the TempDB files to be maxed in size to equally fill the drive you are moving them to – they’re the only residents so they might as well have as much room as they could possibly need.

3. Restart the SQL Server Service

Before you restart the SQL Server service, run the original query to check that all the settings changes for TempDB files have stuck as you intended. Once you restart the SQL Server service, these settings will take effect.

In reality, this should be a 30-second ordeal. I’m going to plead a magnitude of 10 napkin math estimate here – your environment might be 3 seconds or it might be 300 seconds. Get yourself a maintenance window, and restart the MS SQL Server service.

4. Verify

Check through the file system that the new TempDB files have been created. Once you’ve verified that the new TempDB files are in place, you can remove the old TempDB files (they are not automatically removed).

TempDB file layout for a 4-processor server on a teeny tiny 5GB drive

You’re done with your TempDB move and/or file addition! If you’d like to dig more into TempDB performance I can recommend the links below – but, and this is important – restarting the MS SQL service erases helpful query history. If you want to troubleshoot TempDB performance and there isn’t an emergent situation, I’d recommend digging into the performance issues before moving the files.

Resources

DrewSK.Tech

How To Move TempDB on SQL Server

Why?

Move TempDB

1. TempDB’s New Location

2. T-SQL To Update TempDB’s Location

oooooo 8 processors

3. Restart the SQL Server Service

4. Verify

TempDB file layout for a 4-processor server on a teeny tiny 5GB drive

Resources

Basics From Microsoft

On Disk Layout Best Practices

TempDB Performance

It All Went To Shit