Moving tempdb to other directory


After installing a few standalone SQL Servers yesterday, I today discovered that the tempdb databases were located in invalid directories according to EVRY installing guide. The tempdb should be located on another disk than the data and system databases. In our project we have allocated the k: drive for the tempdb.mdf and the templog.ldf should be located together with the other log files.

You can use the below SELECT to find out where tempdb files currently are located:

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

In my case, these where installed in the SQL Server default location on the c: drive, and I needed to move the MDF (data) file to k: drive and LDF (log) to the j: drive. I used the following statements to configure the new directories for tempdb:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'J:\MSSQL\Data\templog.ldf');
GO

You need to restart SQL server services, and the temp files will be recreated, and you can delete the old files.

Advertisements

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