question

jchendorain avatar image
jchendorain asked

Steps to remove tempDB data files

We have SQL 2012 service pack 3 in a clustered with two instances. I am moving the tempDB files to a new cluster disk, so it is necessary to delete the extra tempDB files, so that I can re-create in the new area. I performed this same task in our Dev. environment and now I need to perform the same steps in a Prod environment, and it was a total pain. Each file was in-use, so I had to for each file run the cmds below while restarting the main SQL services multiple times: DBCC SHRINKFILE('tempdev8', EMPTYFILE) and then when that threw an error "could not be removed because its a work table page", I ran: DBCC FREEPROCCACHE And then ALTER REMOVE, all while having to restart the main SQL service and analysis service (msc.exe) numerous times at different steps to be able to remove the files. So is there a better way to accomplish this tempDB removal? Thanks
tempdb
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Erin Stellato wrote a blog about it, when running into the same problem as you: https://www.sqlskills.com/blogs/erin/remove-files-from-tempdb/ In short what you can do is: - Start SQL Server with minimal configuration - ALTER DATABASE tempdb REMOVE FILE - Restart the instance in normal mode - Physically remove the files from the file system
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jchendorain avatar image
jchendorain answered
Thanks so late last night (Maintenance window) I went for it and ran the **EMPTY FILE** followed by the **FREEPROCCACHE** cmd and tried to find a pattern on why some of the 8 files would allow me to remove, and other files required me to restart the main SQL service. And I don't have to the option currently to start the instance in minimal config. mode. Thanks for the response.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sjimmo avatar image
sjimmo answered
The easyiest way to move the tempdb files is to run the following: USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ':\tempdev.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ':\tempdev.ldf') GO Restart SQL Server and then you can go in and delete the old files. Steve
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.