TempDB for Performance Part-II

Introduction

One of my article is related to TempDB for Performance, published on 28^th Feb 2015. Those who are not read it yet can read it from

http://sqlknowledgebank.blogspot.in/2015/02/tempdb-for-performance.html

Now the question came to mid how to change the location of the TempDB from current location to other drive. Here in this article, I am providing the T-SQL command for that. We are not going to discuss about the performance related factors of TempDB. As you can find it form our previous article mentioned above.

In Which Drive My TempDB is Currently Located

USE tempdb

GO

EXEC sp_helpfile;

So we find that the MDF and LDF file of the temp DB is located on

C:\Program Files\Microsoft SQL Server\MSSQL11.JOYDEEPSQL12\MSSQL\DATA

How We Move the TempDB to Another Drive

Suppose we want to move the TempDB from current location to E:\TEMPDB\

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

Hope you like it.

Posted by: MR. JOYDEEP DAS