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')
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.