How to move tempdb

How to move tempdb ? So in this post, I show you what is involved to do this and what you shouldn’t try and do.

This is how to move tempdb

You will need to run run some T-SQL and restart the sql server service to complete the operation.

The logical file name values will need to be obtained and there are a couple of ways to do that.

You can either view the logical file names by accessing the database properties and clicking on “Files” or you can run a script. See screen shots below.

You can also run this T-SQL:
[sourcecode language=’sql’]SELECT name, physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO[/sourcecode]
On my machine, the results were shown in Management Studio as below and here I am interested in the values in the “name” column. You will need these for the next script.

Once you have the logical file names for tempdb, you can run the following script after you have edited it with suitable values for your system. In this example, I am going to set the new path to be another folder on the C: drive of my test server but it is likely that you would be moving tempdb to another drive if this were a real scenario. Whatever path you choose, ensure that the account which the sql server service is running under has permission to read and write to it.
[sourcecode language=’sql’]USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf’);
GO[/sourcecode]
Note that the destination path must exist otherwise the script will fail to run.

If the script completes successfully, you will see the following message:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now restart your sql server service.

Move tempdb – don’ts

Don’t try and move tempdb using a backup and restore method as you will receive an error

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

I hope you have found this information useful on how to move tempdb and if you would like to watch a video on this topic, you can do so right here.