SQL Server - How to move tempdb database to different location

Question

I am creating indices for my tables in SQL server database, but I don't have enough room on C drive and because of that SQL server gave the following error.

Error: Exception Found:
Type: System.Data.Odbc.OdbcException
Message: ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not allocate space for object 'dbo.SORT temporary run storage:  422216087699456' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
ERROR [01000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.

I have enough space on F drive, so how can I move tempdb from C drive to F drive?

Answer 1

You need to run some queries to move the location of tempdb database from the current location to a new location. If you have SQL server management studio, you can run these queries from there.

Steps are as follows:

1. Determine the logical file names of the tempdb database and their current location on the disk. 

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

2. Change the location of each file by using ALTER DATABASE. Change the FILENAME value according the new location on your machine.

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
   GO

3. Stop and restart the instance of SQL Server. 
4. Verify the file change.

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

5. Delete the tempdb.mdf and templog.ldf files from the original location.

Source: microsoft