Move TEMPDB : SQL Server 2005 / SQL Server 2008

As we know, by default, tempdb is placed on the same drive where SQL Server Instance installed on. Sometime its happened that where its installed, that disk space is running in very tight position or disk I/O operation is very slow or not performing upto mark. The solution is to move tempdb to a spacious and faster drive and improve performance, follow these steps:

1. Open SQL Server Management Studio.

Click on Start -> Programme -> MicroSoft SQL Server 2005 / MicroSoft SQL Server 2008 -> SQL Server Management Studio

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane to check the TEMPDB location

SELECT name, physical_name AS CurrentLocation FROM
sys.master_files

WHERE database_id =
DB_ID(N’tempdb’);

GO

5. Now, Suppose you want to move TEMPDB at D:\SQLDATA, simply copy and paste the following in the query pane.

USE
master

GO

ALTER
DATABASE tempdb

MODIFY
FILE (NAME = tempdev, FILENAME
=
‘d:\SQLData\tempdb.mdf’);

GO

ALTER
DATABASE tempdb

MODIFY
FILE (NAME = templog, FILENAME
=
‘d:\SQLData\templog.ldf’);

GO

6. Click Execute, result may be like this,

7. Now, We have to restart SQL Server Service using via Start-> Run -> Services.msc

Or

Start -> Programme- MicroSoft SQL Server 2005 / MicroSoft SQL Server 2008 ->Configuration Tools -> SQL Server Configuration Manager

8. Stop and Start SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Copy and paste the following to verify that tempdb has moved to the new location:

SELECT name, physical_name AS CurrentLocation FROM
sys.master_files

WHERE database_id =
DB_ID(N’tempdb’);

GO

11. Click Execute.

12. In the physical_name column, you should see the path to the new location.