Move tempdb files in SQL Server

Tempdb is a SQL Server system database popularly known as SQL Servers garbage bin mainly because of its usage. The Tempdb database is created at the same location as other system databases during SQL server installation. An important aspect of Tempdb is that it is recreated each time a SQL Server instance is started. It is suggested to move tempdb files to separate disk to improve performance or to deal with file size issues.

The first step is to get the logical file names of tempdb database. This is done by running the below query.

Move tempdb files in sql server

The next step is to set the new physical location for each of the logical tempdb files. This is done as shown below.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\Tempdb\templog.ldf');

The above query sets the file location for tempdev file to ‘D:\tempdb’ and templog to ‘E:\tempd’ respectively.

As the Tempdb is recreated every time SQL Server instance is started, the last step is to restart SQL Server instance so that the tempdb is recreated at the new location. The files at previous location can now be deleted if need be.

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *