I moved the tempdb first locating the files:
use master select name, physical_name from sys.master_files where database_id = DB_ID('tempdb'); GO
then I moved each file:
use master go alter database tempdb modify file (name = tempdev, filename = 'S:\') go alter database tempdb modify file (name = templog, filename = 'S:\') go alter database tempdb modify file (name = tempdev2, filename = 'S:\') go
The ndf tempdev2 did not move, though the path was set. My SQL Server will not start unless I use the "/f" switch. When I retry the move or set the path to original location I get the following message:
Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File 'tempdev2' does not exist.
Any ideas? tempdev and templog are fine, just the tempdev1 ndf. Is it possible to set the path for tempdev2 via the registry?
The mistake I made was probably not shrinking the ndf before the move. Now I can not shrink it because the path is not correct.
I know get the following error in event viewer:
CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file 'S:\tempdb'.
I checked permissions on the partition and folder, and everyone has full access and this permission error happens.
You are not providing a unique physical filename for each file. You should alter tempdb like this:
use master go alter database tempdb modify file (name = tempdev, filename = 'S:\tempdev.mdf') go alter database tempdb modify file (name = templog, filename = 'S:\templog.ldf') go alter database tempdb modify file (name = tempdev2, filename = 'S:\tempdev2.mdf') go
Microsoft Docs for ALTER DATABASE file and filegroup options state the following:
To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )