In this post, I am going to share one of the solution for SQL Server error like, One or more files listed in the statement could not be found or could not be initialized.
Today morning, I was doing backup and restore exercise for one my report database. Generally, this database has one attached .mdf and .ldf file.
I checked the database file properties and found one orphaned .ldf file also attached to this database. Physically was showing me only two files, but database file properties were showing me three files.
When I tried to remove this orphaned file, I got this error:
Msg 5009 One or more files listed in the statement could not be found or could not be initialized.
Below are two different approaches to solve this problem:
Option 1: First, Try this quick way:
Take a LOG backup and your orphaned file entry will gone from the database.
Option 2: Second, follow the below steps:
Add another filegroup:
ALTER DATABASE [Employee] ADD FILEGROUP TempFG;
Add another file in this new filegroup and with a different logical name, but in the same path:
ALTER DATABASE [Employee] ADD FILE ( NAME = N'remove_templog', FILENAME = N'C:\DBBackup\Temo_log.ndf', SIZE = 1MB, MAXSIZE = 1MB) to FILEGROUP TempFG
Now check this table sys.sysaltfiles, file id reused by newly created file.
SELECT *FROM sys.sysaltfiles;
Now removed the newly created file:
ALTER DATABASE [Employee] REMOVE FILE remove_templog;
Now try to drop your orphaned file:
ALTER DATABASE [Employee] REMOVE FILE emp_log1;
Drop newly created dummy file group:
ALTER DATABASE [Employee] REMOVE FILEGROUP TempFG;
Last, Take a full LOG backup and check your database file properties, the orphaned file should be removed now.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.