SQL: Move “tempdb” database

When SQL Server is installed, the “tempdb” database is placed by default, on the same drive that SQL Server is installed on.

As this is usually the same drive as the operating system, it is best to move the “tempdb” database to a separate drive.

To move the “tempdb” database to a separate drive to improve performance, follow these steps 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server.:

    1. Open “SQL Server Management Studio”.
    2. Connect to the relevant server.
    3. Click the “New Query” button.
    4. Copy and paste the following into the query pane:
USE master;
MODIFY FILE (NAME = tempdev, FILENAME = '{location}\tempdb.mdf');
MODIFY FILE (NAME = templog, FILENAME = '{location}\templog.ldf');
    1. Change the {location} reference in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.
    2. Click Execute.
    3. Open “Control Panel” and then select the “Administrative Tools”, and select “Services”.
    4. Stop and Start (or click on Restart) the “SQL Server (MSSQLSERVER)” service.
    5. Go back to “SQL Server Management Studio” and open a new query pane.
    6. Copy and paste the following to verify that tempdb has moved to the new location:
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
  1. Click Execute.
  2. In the “physical_name” column, you should see the path to the new location.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.