SQL Server- Move TempDB to new location

Why and How to move TempDB to new location

Why:

There are mainly two reasons why you would like to move TempDB to a new location:

1. TempDB has grown big and existing drive doesn’t have enough disk space.

2. Moving TempDB to a new disk which is faster to improve the performance.

 

How:  Follow the steps to move TempDB to new location

First of all, check out the current location of TempDB files by executing the below query.

use tempdb

go

exec sp_helpfile

 

1

 

This will provide you the current location of tempdb.

now to move the tempdb to new location, execute the below command

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

 

2

 

I am moving tempdb from (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\) to (C:\Tempdb\) as i have only one drive in my machine. You can change it accordingly.

To make changes come in effect, restart SQL Service.

 

3

After restarting the sql services, check the location of tempdb now by running the below query.

use tempdb

go

exec sp_helpfile

4

 

 

Tempdb has been moved to a new location.

 

Hope this was helpful !!!!!

Advertisements
Leave a comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: