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
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
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.
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
18 People are following this question.
