Moving tempdb file location for SQL Server 2008 R2

Problem

You’ve ran out of disk space on the drive where the tempdb files are located.

Solution

You need to move the tempdb files to a new location with sufficient disk space.

1. Execute the following query:

   USE master; GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = tempdev, FILENAME = '[new_path]\tempdb.mdf');
   GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = templog, FILENAME = '[new_path]\templog.ldf');
   GO

2. Stop and restart the instance of SQL Server.
3. Once the service has been restarted, verify the changes using this query:

   sp_helpdb tempdb

Reference

• http://msdn.microsoft.com/en-us/library/ms345408.aspx