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

Sensu is an open source monitoring event pipeline. Try it today.

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.


Sensu: workflow automation for monitoring. Learn more—download the whitepaper.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}