Moving TempDB Database Files on MSSQL 2012 R2.

I ran into a disk space situation on a new database server and decided to move the Tempdb files to a new drive location. My first thought was to detach the database and move the files.

It turns out you can’t detach a tempdb database since it’s a system database. The following T-SQL query helped achieve the task.

Determine the Logical Names of the TempDB Data and Log files:

Login to SQL2012 Management Studio.

Expand the Databases tree and expand the system databases tree.

Select and right-click on the TempDB database.

Select Properties and on the properties window, note the tempdb files logical names as indicated below:

temp1

Use the following script to move the files Edit the file name parameter values to reflect the destination path :

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'E:\MSSQLData\Tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'F:\LogFiles\Tempdb.ldf');
GO

Restart the SQL Instance after the move.

I verified that the file path was changed . I finished by removing the old temp files from the previous location.

Advertisements
This entry was posted in MSSQL 2008 R2, MSSQL 2012 and tagged , , . Bookmark the permalink.

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