SQL Server: Error Msg 5009-One or more files listed in the statement could not be found or could not be initialized

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:

Add another file in this new filegroup and with a different logical name, but in the same path:

Now check this table sys.sysaltfiles, file id reused by newly created file.

Now removed the newly created file:

Now try to drop your orphaned file:

Drop newly created dummy file group:

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.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar

wpDiscuz