TempDB Movement to different drive

We might land up with a requirement due to space constraints, where it is required to move TempDB files to a bigger drive. To achieve the same, follow the below listed steps where I will be moving the files from C:\ drive to D:\ drive ->

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

Along with other information related to the database. The names of the files are usually tempdev and templog by default. These names will be used in next statement.

Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘d:datatempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘e:datatemplog.ldf’)
GO

Note- Restart SQL Server services to bring this change in effect and you are done with this exercise.

Happy learning.

Cheers!
Aman Kharbanda

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s