Cannot change logical file name: The logical file name “PROD1” is already in use. Choose a different name

Question

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.

Answer 1

Try this:

What you have to do is, first set it to another name.

ALTER DATABASE [PROD] 
MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1Temp')
GO

The file name 'PROD1Temp' has been set.

Then change it back to PROD1 so that you can achieve your target.

ALTER DATABASE [PROD] 
MODIFY FILE (NAME=N'PROD1Temp', NEWNAME=N'PROD1')
GO

Some helpful links :

'Logical file name "MyDB" is already in use' Even though the database doesn't exist

https://stackoverflow.com/questions/7534664/sql-server-script-error-database-is-already-in-use