How to Relocate Microsoft SQL Server's tempdb Files

On this page:

Introduction
Using "SQL Server Management Studio"
Using "SQLCMD.EXE
Starting a SQL Instance in a Command-Prompt Window.

Introduction   Microsoft SQL Server's heavy use of temporary files can result in an I/O performance bottleneck. The non-persistent files used by SQL to store system data are called 'tempdb', and are literally a temporary database consisting of two files: one a table file, the other a log file. Moving these files to a high-performance storage device, such as a RAM disk, can dramatically improve SQL Server performance. Because the files are truly temporary -- they are, in fact, re-created each time SQL is started -- there is no requirement that these files be located on a persistent storage device.
   

To move the tempdb files from their current location to a higher-performance storage device requires that the SQL Server instance be running and that it execute several queries. Outlined below are two sets of query commands. The first set changes the locations of the temporary database and log files (tempdb.mdf and templog.ldf). The second set reports the file location settings.

QUERY TO CHANGE FILE LOCATION (ChangeLocation.sql)
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf')
go

Note: The two R:\temp\tempxx.xxx filenames specify the new locations for the tempdb database and log files. SQL requires that the tempdb files NOT be stored in volume's root directory.

QUERY TO REPORT FILE LOCATION (MyReportScript.sql)
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb')
go

These query commands can be executed by the SQL instance using the "SQL Server Management Studio" graphical user interface tool, or using the SQLCMD.EXE command-line tool. Examples are provided for both tools. Go to top
Using "SQL Server Management Studio"  
To change the location using "SQL Server Management Studio" (requires that the SQL instance is running as a service):
1. Open "SQL Server Management Studio", and connect to the desired server.
2. Open a new query, copy the Change File Location text into the query pane, and execute the query. (Change the path as required.)
3. Open "Services" (Control Panel -> Administrative Tools), and stop and then start "SQL Server (Xxx)". ('Xxx' is the instance name.)
4. In "SQL Server Management Studio" open a new query, copy the Report File Location text into the query pane, and execute the query.
5. Verify the new file locations in the physical_name column.
Using SQLCMD.EXE  

To change the location using the command-line tool SQLCMD.EXE, open a command-prompt window with Administrator privileges, and cd to the instance's Binn directory. Then follow one of the three methods shown below:

Note: In the examples below,
'MY_SERVER' is the name of the server running SQL.
'SQL_INSTANCE' is the name of the SQL instance.
Go to top
First Method:
Scripted
 

Create the two script files, 'ChangeLocation.sql' and 'ReportLocation.sql', with the content shown in the tables above. Modify the file path specification appropriately.

To execute the scripts, open a command prompt and enter the commands shown below:
sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ChangeLocation.sql"

Changed database context to 'master'.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

sqlcmd -S "MY_SERVER\SQL_INSTANCE" -i "ReportLocation.sql"

name physical_name
------------------ -------------------
tempdev R:\temp\tempdb.mdf
templog R:\temp\templog.ldf
(2 rows affected)  


Go to top
Second Method:
Interactive
   Execute the commands in an interactive SQLCMD session, as shown below (modify the file path specification appropriately):
sqlcmd -S "MY_SERVER\SQL_INSTANCE"
1> USE master
2> go
Changed database context to 'master'.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf')
2> go
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf')
2> go
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
1> quit

sqlcmd -S "MY_SERVER\SQL_INSTANCE"
1> SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb')
2> go

name physical_name
------------------ -------------------
tempdev R:\temp\tempdb.mdf
templog R:\temp\templog.ldf
(2 rows affected)  
1> quit

Go to top
Third Method:
Command-Line Query 		  The query commands can be concatenated for use in a single argument on the command line, as shown below (modify the file path specification appropriately):
sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"USE master;ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:\temp\tempdb.mdf');ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:\temp\templog.ldf');"

Changed database context to 'master'.
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

sqlcmd -S "MY_SERVER\SQL_INSTANCE" -Q"SELECT name, physical_name;FROM sys.master_files;WHERE database_id = DB_ID('tempdb');"

name physical_name
------------------ -------------------
tempdev R:\temp\tempdb.mdf
templog R:\temp\templog.ldf
(2 rows affected)  

Starting a SQL Instance in a Command-Prompt Window   If the SQL instance fails to start as a service, it can be started in a command-prompt window. Generally, this is only done for troubleshooting purposes. (See http://msdn.microsoft.com/en-us/library/ms180965(v=SQL.100).aspx) For example, if a SQL instance starting up as a service cannot find the storage device that the tempdb files are to be stored on, the instance will fail startup. Since the service cannot be started, its tempdb location also cannot be reconfigured in the normal way. In this case, the solution is to run the instance in a command-prompt window, reconfigure the tempdb location, stop the command-prompt instance, and start it again, running as a service. Go to top

To start a SQL instance in a command-prompt window, cd to the instance's Binn directory (eg. 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL_INSTANCE\MSSQL\Binn'), and enter the following command:

sqlservr -f -s "SQL_INSTANCE"

This command runs the SQL instance named "SQL_INSTANCE" inside the command-prompt window itself, with a minimal configuration. A minimal configuration limits instance execution to a single user.

To reconfigure the tempdb file locations, open another command-prompt window and use one of the SQLCMD.EXE methods shown above. When finished, return to the command-prompt window in which SQL is running, type Ctrl-C, and confirm the shutdown request. Afterward, run the SQL instance as a service.
    See also:
Optimizing tempdb Performance
Microsoft SQL Server Performance Solutions
     

Last updated July 7, 2010


Citrix   HP   IBM

  Home - Site Map - Contact Us - Legal Notices - Privacy Policy  

SuperSpeed LLC is the owner of the following U.S. Patents 5577226, 5606681, 5918244, 6370615, 6629201, 6651136, 7017013, 7039767, 7111129, 7475186, 7594068, 7631139, 7886099 and other patents are pending.

Copyright © 1996-2012 SuperSpeed LLC. All rights reserved.