Moving TEMPDB in SQL Sever 2005

 

As advised by Microsoft I needed to move the TEMPDB and logs away from the C: drive, in this case I have moved onto a folder called TEMPDB on F:

The following code shows the current location of TEMPDB and logs;

USE TempDB
GO
EXEC
sp_helpfile
GO;

you should see something like this

tempdev 1
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
PRIMARY
6608000 KB
Unlimited
10%
data only

templog 2
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
NULL
29504 KB
Unlimited
10%
log only

The following code creates new Tempdb and logs in the new location provided;

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

The new current location of TEMPDB and logs is;

tempdev 1
F:\TEMPDB\tempdb.mdf
PRIMARY
145792 KB
Unlimited
10%
data only

templog 2
F:\TEMPDB\templog.ldf
NULL
768 KB
Unlimited
10%
log only