Monday, 14 October 2013

How do I move tempdb location SQL Server

This is how to move your tempdb location.

Why?

Maybe they have grown too big and the existing drive does not have enough space.

It can improve database disk read speed as they can be read in parallel.

Below are instructions to move the existing tempdb files to new drives, g and h.

Open management studio and connect to your server. Start a new query, and run the following sql to get the current location of your tempdb files:

USE TempDB
GO
EXEC sp_helpfile
GO

The results should show the name of the files and their location.

The names of the files are usually tempdev and templog. Location should be something like:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

Remember the names, they will be used in the next statement:

Run the sql below to move mdf and ldf tempdb files.

USE master
GO

ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO