• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Sql Server 2012 - Moving multiple tempdb files to a different Partition

Hello Everyone,

Our tempdb is made up of 16 different files.  I need to move the tempdb to a different partition.  I found the script below to move the tempdb, but I was not sure how to move all the multiple files.  Should I add every file tempdev1, tempdev2, tempdev3...etc... to this script?



use master
Alter database tempdb modify file (name = tempdev, filename = 'F:\program files\microsoft SQL server\tempdb.mdf')
Alter database tempdb modify file (name = templog, filename = 'F:\program files\microsoft SQL server\templog.ldf')
1 Solution
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It's not necessary to specify the MODIFY FILE for each file in your database. You only need to specify the files that need to be moved.

By the way, I trust you have gone through the recommendations from Microsoft with respect to moving of system databases: https://msdn.microsoft.com/en-us/library/ms345408.aspx
Scott PletcherSenior DBACommented:
Yes, you need to specify a separate command for every file.  Then, when SQL Server cycles (stops and restarts), the tempdb files will be created in the new location.  The old tempdb files will still be in the original location, you will have to delete them yourself.

Btw, 16 is very likely too many tempdb data files.  You need to review that as well.
bjenningsAuthor Commented:
Thank you so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now