Why and How to move TempDB to new location
Why:
There are mainly two reasons why you would like to move TempDB to a new location:
1. TempDB has grown big and existing drive doesn’t have enough disk space.
2. Moving TempDB to a new disk which is faster to improve the performance.
How: Follow the steps to move TempDB to new location
First of all, check out the current location of TempDB files by executing the below query.
use tempdb
go
exec sp_helpfile
This will provide you the current location of tempdb.
now to move the tempdb to new location, execute the below command
USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\Tempdb\tempdb.mdf‘)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘C:\Tempdb\templog.ldf‘)
GO
I am moving tempdb from (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\) to (C:\Tempdb\) as i have only one drive in my machine. You can change it accordingly.
To make changes come in effect, restart SQL Service.
After restarting the sql services, check the location of tempdb now by running the below query.
use tempdb
go
exec sp_helpfile
Tempdb has been moved to a new location.
Hope this was helpful !!!!!
Siddharth Verma