Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

TempDB for Performance Part-II

DZone's Guide to

TempDB for Performance Part-II

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Introduction


One of my articles is related to TempDB for Performance, published on 28 th Feb 2015. Those who have not read it yet can find it here:
Now the question came to mind how to change the location of the TempDB from the current location to another drive. In this article, I am providing the T-SQL command for that. We are not going to discuss the performance related factors of TempDB, as you can find it from our previously mentioned article.

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.


Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}