• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to move TempDB to another drive?

December 10, 2016

 

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.

 

For example:

 

"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

 

OR

 

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.

 

 

 

 

Please reload

RECENT POST
Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon