Technical FAQs

Ask a Question

How to move SQL Server Tempdb to a different location

User may be required to move the SQL Server 'tempdb' files from one location to another.

Product Line
SQL Server

SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

The tempdb is used extensively during many SQL Server database operations. At times, given certain types of transactions, the tempdb files (tempdb.mdf and templog.ldf) can grow very large and run out of disk space. These transactions will fail if tempdb runs out of drive space. It may be necessary to move the SQL Server tempdb files to a location in which more drive space is available.

The following information is included with the event.

D:\Program Files\Microsoft SQL Server\MSSQL10_50.STXPWRMON\MSSQL\DATA\templog.ldf
112(There is not enough space on the disk.)

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*  

- Run the Windows Disk cleanup tool to free up disk space and delete temprorary files.
- Move the 'tempdb' database and log files to another location by following the steps below:

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
  1. Determine the logical file names of the tempdb database and their current location on the disk by executing the following query: 

    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 by executing the following query in SQL:

    The new location of the tempdb database mdf and ldf files is defined as highlighted below, this may be modified to meet your requirements.
    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 by executing the following query in SQL:

    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.
Was this helpful?
What can we do to improve the information ?