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.
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
