Moved 'tempdb', now file 'tempdev2' does not exist

by Logman   Last Updated April 20, 2018 17:06 PM

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.

Additional information

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.



Answers 1


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 ' )  
Max Vernon
Max Vernon
April 20, 2018 16:51 PM

Related Questions


Unable to remove additional tempdb file

Updated February 07, 2018 02:06 AM

TempDB data files don't shrink well on SQL 2008

Updated April 03, 2018 08:06 AM



tempdb contention issue and excessive pagewrites/s

Updated April 12, 2015 20:02 PM