How to move TempDB to another drive?
Basically, moving database TempDB is very simple task. You need to run two queries and restart sql service.
Why move TempDB to another drive? Three major reasons:
1) The Drive is full and there is not for TempDB enough space.
2) It is prefered to have TempDB on a different drive for better performance.
3) We added new SSD disk for TempDB, because we want to improve performance our database instance.
When we install a new database instance, TempDB is located in the default path for System databases.
"C:\Program Files\Microsoft SQL Server\MSSQL13.ADS\MSSQL\DATA"
There are two ways how to find out where is TempDB placed:
In Management Studio - Right click on tempdb in systems database - Click Properties - File
You can run this query below:
In my case, It looks like this:
The next step is to change the location for tempdb(.MDF,.NDF and .LDF)
In this case, we use this following script:
If we were successful, the results are something like:
At the end we must restart SQL Server in order for changes to take effect. SQL server is now going to be able to create TempDB files in new locations.
I hope this post was helpful.