How to move TempDB from one drive to another?

Suppose tempdb database files are located on drive C and you want to move it to drive E.

To get the current location of the tempdb database files run the following script.

USE TempDB
GO
EXEC sp_helpfile
GO

The result is like below

Name	FileID	FileName	FileGroup	Size	Maxsize	Growth	usage
tempdev	1	C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\MSSQL\ DATA\tempdb.mdf	PRIMARY	18240 KB	Unlimited	10%	data only
templog	2	C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\MSSQL\ DATA\templog.ldf	NULL	768 KB	Unlimited	10%	log only

The Name field in above result will be used in 'ALTER DATABASE' statement to move mdf and ldf files

USE master
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = tempdev, FILENAME = 'e:TempDB\tempdb.mdf')
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = templog, FILENAME = 'e:TempDB\templog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

NOTES:
1. Reasons why tempdb needs to be moved
       a. No Enough space on existing drive for Tempdb to grows big in size.
       b. Moving Tempdb to another drive helps to improve disk I/O.
2. Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB.
3. It is recommended to create the tempdb files equal to the number of CPU cores in the server.

What is TempDB Database in SQL Server?

The tempdb is a system database, which is global resource available to all users.

The tempdb is a temporary workspace for storing temporary tables, worktables that hold intermediate results during the sorting or query processing which intern increase the performance for SQL Server.

The tempdb is re-created each time the SQL Server service starts with clean copy of the database. The tempdb is created from model database and reset to its last configured size.

You can shrink tempdb but shrinking files regularly is not a recommended practice, because these files may probably grow again and also shrink operations causes’ data fragmentation. 

Backup and restoration operations are not possible on tempdb database.

Major Problems with TEMPDB:-

1. tempdb has run out of space.

2. tempdb is experiencing I/O bottleneck

3. tempdb is experiencing contention

In my next post i will document how to move TempDB database from one drive to another.