Every SQL Server instance relies heavily on tempdb to function. Because of this it is important to properly setup and configure tempdb. If you work on a system with a sub optimal tempdb setup then you may have to move files. Read on to see a technique for getting this job done.

Review tempdb Configuration Best Practices

Since tempdb is used by all databases on the instance it is imperative to properly configure it. I wrote about how to set up smarter configurations in my SQL Server Environmental Diagnostics Guide.

The basic guidelines are:

Each tempdb data file should be the same initial size
Autogrowth to tempdb files should be an explicit value in MB instead of a percentage. Choose a reasonable value based on the workload. Ex. 64MB, 128MB, 256MB, etc.
The number of tempdb files should be 1 per logical processor core up to 8. At that point the performance should be monitored and if more tempdb files are needed they should be added in sets of 4.
Ideally the tempdb files are sized up to the max they will need and never have to autogrow.
Use trace flags 1117 and 1118 for versions of SQL Server < 2016. In SQL Server 2016 these trace flags are defaults.
- Trace flag 1117: when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow together
- Trace flag 1118: Removes most single page allocations on the server, reducing contention on the SGAM page. TLDR; no more mixed extents – use the whole page.

Here is an example from the wild of how not to configure tempdb.

There is a lot to unpack here. Pretty much everything that could be wrong is wrong. You may think that things are not this bad in the wild but you would be wrong.

The background is that there are about 40 logical CPU cores. I have no idea why there are 60+ tempdb data files because that is way too many. Not only are there diminishing returns to adding more files unnecessarily but it can actually hurt performance. Start with 8 and monitor.
Every tempdb file has a different initial size. This is really bad because the round robin and proportional fill algorithms that SQL Server uses to allocate tempdb files depends on the files growing together. Paul Randal has a very interesting post about this.
Autogrowth are set to percentages. This is much harder to manage than using concrete MB. The math changes the larger you get: 10% of 1GB is much different than 10% of 1TB.

Shake the horror off and let us see about what we can do to improve the situation.

Move and Configure tempdb

My tiny VM lab has 2 CPU cores. Let’s look at how we can move tempdb from where it currently resides to another location. We will reconfigure it along the way.

How many virtual CPU cores do you have? You can look at task manager to see:

Alternatively you can query:

--how many logical CPU cores
select scheduler_id, cpu_id, status, is_online 
from sys.dm_os_schedulers 
where status = 'VISIBLE ONLINE';

--how many logical CPU cores

select scheduler_id, cpu_id, status, is_online

from sys.dm_os_schedulers

where status = 'VISIBLE ONLINE';

Here is our starting location:

select * from sys.master_files where database_id = DB_ID(N'tempdb');

1	select * from sys.master_files where database_id = DB_ID(N'tempdb');

The files live here:

Now let’s put the files somewhere else and change the initial size along with the autogrowth.

--Move tempdb files to a different location
declare @sql nvarchar(max) = '';					--initialize because we cannot concatenate NULL with anything
declare @newPath varchar(128) = 'G:\MSSQL\tempdb\';	--ENTER NEW LOCATION
declare @sizeMB int = 128;							--ENTER INITIAL SIZE IN MB
declare @autogrowthMB int = 64;						--ENTER AUTOGROWTH IN MB

select @sql += N'alter database tempdb modify file (name = [' + f.[name] + '],
	filename = ''' + @newPath + f.[name]
	+ case when f.[type] = 1 then '.ldf' else '.mdf' end + ''''
	+ ', size = ' + cast(@sizeMB as nvarchar) + 'mb'
	+ ', filegrowth = ' + cast(@autogrowthMB as nvarchar) + 'mb'
	+ ');'
	+ char(13) + char(10)
from sys.master_files f 
where f.database_id = DB_ID(N'tempdb');

print @sql;
exec sp_executesql @sql;

--Move tempdb files to a different location

declare @sql nvarchar(max) = ''; --initialize because we cannot concatenate NULL with anything

declare @newPath varchar(128) = 'G:\MSSQL\tempdb\'; --ENTER NEW LOCATION

declare @sizeMB int = 128; --ENTER INITIAL SIZE IN MB

declare @autogrowthMB int = 64; --ENTER AUTOGROWTH IN MB

select @sql += N'alter database tempdb modify file (name = [' + f.[name] + '],

filename = ''' + @newPath + f.[name]

+ case when f.[type] = 1 then '.ldf' else '.mdf' end + ''''

+ ', size = ' + cast(@sizeMB as nvarchar) + 'mb'

+ ', filegrowth = ' + cast(@autogrowthMB as nvarchar) + 'mb'

+ ');'

+ char(13) + char(10)

from sys.master_files f

where f.database_id = DB_ID(N'tempdb');

print @sql;

exec sp_executesql @sql;

I usually run it first with just the print statement and not the subsequent execution. That way I can give it a sanity check before letting it rip.

PROTIP: leave a print for variables to help debugging

Here is what the SQL will be:

Now we can execute:

NOTE: you must restart SQL Server services to see the change in tempdb!

After restarting services here are the new files:

The old files will still be here: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA

Since nothing is pointing to them you can simply delete the files from this location.

Removing Files from tempdb

If you need to remove some tempdb files there is a trick to it. SQL Server will not let you remove the file unless it is emptied.

dbcc shrinkfile(tempdev2,emptyfile);

1	dbcc shrinkfile(tempdev2,emptyfile);

Now the file is safe to remove:

alter database tempdb remove file tempdev2;

1	alter database tempdb remove file tempdev2;

The file will now be gone from tempdb:

If you check the file location you will see that it is no longer there.

Thanks for reading!

if you liked this then you might like Migration – How To Move SQL Server Database Files to a New Location

Did you find this helpful? Please subscribe!

One thought on “How to Move and Configure tempdb Files”

Configuring tempdb – Curated SQL says:

August 13, 2018 at 7:01 am

[…] Jeff Mlakar looks at some basic guidelines for tempdb and shows how to configure this database: […]