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.
|
1 2 3 4 5 |
-- To find logical name for the temp data and log file USE TempDB GO EXEC sp_helpfile GO |
OR
|
1 2 3 4 5 6 7 8 9 10 |
-- To find logical name for the temp data and log file Use master GO SELECT name AS [LogicalName], physical_name AS [Location], state_desc AS [Status] FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO |
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.
|
1 2 3 4 |
USE master GO ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ) Go |
Example
|
1 2 3 4 5 6 |
USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'x:\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'x:\datatemplog.ldf') GO |
Now restart SQL services, the change will take place.