0

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.

|improve this question
  • 2
    It appears you've managed to log out from the account you used to create the question. Now that you are using a different account, you can no longer freely edit or comment on your question (or on its answers), but you can regain full control by requesting a merge of your two accounts. – Andriy M Nov 24 '16 at 14:47
  • @GdB More specifically, the unregistered (cookie-based) account used to ask the question only works on the same device and browser as used initially. Follow the instructions at the link Andriy provided to fix that. Once completed, your registered (login-based) account will own this question. – Paul White♦ Nov 25 '16 at 12:50
  • @GdB - did you ever find a resolution to this? A contractor has left me with a bit of a mess and I can't clean it up due to this error. – Greg Jul 5 '18 at 23:47
3

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.