How to change ‘TempDB’ file location in SQL Server

Scenario

The location of ‘Tempdb’ database files tempdev and templog is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data which is the default location. Due to the large filesize of tempdb.mdf,  We need to change the location of this file to another drive (X).

First, check the location of TempDB Data and Log Files. Execute the below mentioned script to get the logical name for the temp data and log file and also the location of TempDB data and log file.

OR

Now need to change the location of TempDB Data and Log files using ALTER DATABASE statements. Execute the below scripts to change the temp database to the new drive.

Example

Now restart SQL services, the change will take place.


Comments

Leave a Reply

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