This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 |
ALTER DATABASE [Employee] ADD FILEGROUP TempFG; |
Add another file in this new filegroup and with a different logical name, but in the same path:
|
1 |
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.
|
1 |
SELECT *FROM sys.sysaltfiles; |
Now removed the newly created file:
|
1 |
ALTER DATABASE [Employee] REMOVE FILE remove_templog; |
Now try to drop your orphaned file:
|
1 |
ALTER DATABASE [Employee] REMOVE FILE emp_log1; |
Drop newly created dummy file group:
|
1 |
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.
