SQL Server Error 5009: One or more files listed in the statement could not be found or could not be initialized
I had to add a log file to a database and then extend the size of this log file to a specific value. I got SQL Server error 5009 while creating the log file and got same error code again while extending the size of this file. Here i will describe how to fix Microsoft SQL Server Error 5009 while adding a database file or removing a database file or extending the database file size. You can see the screenshot of this error in below image while adding the log file.
Microsoft SQL Server Error 5009 While Adding Database File
I was working on a migration project where we had to load huge data in a SQL Server database on the target server. To optimize data load performance, we had decided to create database with the final size on the destination server. The final size of database post data import was around 800 GB. We made some configurational changes to reduce data import time. Extending database files size was one of the step to reduce data load time. If you want to learn how to speed up data import then I would suggest reading attached article.
We decided to add one more log file on separate drive considering extensive log growth during data load. While adding the log file, we got error 5009. Full error details are given below.
One or more files listed in the statement could not be found or could not be initialized. CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘T:\Techyaz_log3.ldf’.(Microsoft SQL Server, Error: 5009)
As error suggests, I don’t had enough permission to create this file on given location where i was trying to create the log file. There are two solutions to fix this issue. First solution is to set the Operating system permission on the mentioned .ldf file to full permission for your login account and SQL Server service account. While second solution is to change the location to the path where you have enough permission to create or add database files. Any of these two solutions will fix your problem. Let’s go to another version of Microsoft SQL Server Error 5009 while extending the log file.
Microsoft SQL Server Error 5009 While Extending the Size of Log File
When i changed the location to create this ldf file to a directory T:\Data\Techyaz_log3.ldf this time log file was created but we get same issue again while extending the size of this log file. Next, I was assigning bigger value to each data and log files. We had successfully increased data files size in few minutes because we had already enabled Instant File Initialization to skip zeros process on storage but we faced Microsoft SQL Server error 5009 again while increasing the size of log files. i was extending log file with bigger values. As we know Instant File Initialization does not work for transaction log files so extending the size of log files was taking lot of time. This time access denied issue was disappeared but error 5009 issue was there only. Error description for this error is given below.
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
To fix this error, we had passed small value to extend the file size. Earlier we were trying to increase the log file in a slot of 40 GB in single go. It was taking time to finish but most of time log file size got extended with 40GB but sometimes we had received above Microsoft SQL Server error 5009. When we got this error, we extend the size of log file to 10Gb in place of 40GB that we were doing previously. Reducing the value while extending the log file has fixed this issue and log files had been extended to its future size successfully.
Microsoft SQL Server Error 5009 While Removing Database File
Some of the blogs have suggested that they got SQL Server error 5009 while removing a logical file in SQL Server but surprisingly I got this error while creating or increasing the size of log file. So if you are getting this error while removing any database file then you can keep reading this article to solve this issue.
Whenever we add any database file then its entry got captured in system catalog files, if you are trying to delete any database file then there might be possibility that logical name of the file that you try to remove is not unique in the system catalog tables. For example, this issue occurs if the file existed in the database earlier, and then the file was removed. When you remove the file, it will try to drop earlier file that has been removed but its metadata is not cleaned and appear like ghost file.
You should follow below steps to fix this issue.
- Create a new logical file with different name and the same data type. For example, If your file;s logical name is Techyaz_Logical_Name then create this new file with “Techyaz_Logical_Name_New”. Run below command to create this file.
ALTER DATABASE [Techyaz] ADD FILE ( NAME = N'Techyaz_Logical_Name_New', FILENAME = N'T:\DATA\Techyaz_file4.ndf', SIZE = 10MB, MAXSIZE = 100MB)
- Now we will remove the logical file that we have created in step 1, as in the following example:
ALTER DATABASE [Techyaz] REMOVE FILE [Techyaz_Logical_Name_New]
- Run a transaction log backup of the database.
- Now go ahead and remove the logical file that were trying to remove earlier and your received Microsoft SQL Server Error 5009.
Here, I have described multiple possibilities and fixes for Microsoft SQL Server Error 5009. I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- Manage Transaction Log File during Data Load
- How to get total no of virtual log files in a transaction log file
- Fix Error 3023: Shrink failed for Log file
- Understanding SQL Server Transaction Log Architecture
- Fix SQL Server Error 1105: Could Not Allocate Space for Object in Database because the Filegroup is Full
- Fix SQl Server Error 5144 and 5145 for long and failed Autogrow of data and log file
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018