I made an error in the naming of a physical data file. So I dropped the file from within SQL Server, and wanted to use the same logical file name again. But now SQL Server 2014 refused. So for the moment I created it as PROD0, and tried a rename afterwards that.
ALTER DATABASE [PROD] MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1')
Msg 1828, Level 16, State 3, Line 3
The logical file name "PROD1" is already in use. Choose a different name.
If I select:
select name,physical_name
from sys.database_files
order by physical_name
this logical filename does not appear:
name physical_name
PROD D:\MSSQLSERVER\Data\PROD.mdf
PROD0 D:\MSSQLSERVER\Data\PROD0.ndf
PROD2 D:\MSSQLSERVER\Data\PROD2.ndf
PROD3 D:\MSSQLSERVER\Data\PROD3.ndf
PROD4 D:\MSSQLSERVER\Data\PROD4.ndf
PROD5 D:\MSSQLSERVER\Data\PROD5.ndf
PROD6 D:\MSSQLSERVER\Data\PROD6.ndf
PROD7 D:\MSSQLSERVER\Data\PROD7.ndf
PROD_log E:\MSSQLSERVER\Data\PROD_log.ldf
So even if I would like to drop it, I can't, because it does not exist.
If it might be important: this is part of an Availability Group (2 nodes only). I made the changes on the primary of course. The changes were correctly propagated to the secondary, so also there the file 'PROD1' is not there.
How can I succeed in renaming PROD0 to PROD1?
Edit after the answer of SQLBoy: Sorry, that did not work; same error. The logical Filename PROD1 seems to be hidden somewhere deep in the datadictionary.