FCB::Open failed: Could not open file

Question

The machine was restarted while I was shrinking a database and since then the SQL Server (MSSQLSERVER) not starting. After reading the blogs I replaced the master.mdf and mastlog.ldf files from the templates folder with the same file, assuming master database was corrupted as I was shrinking this.

However the problem remains and the error log file gives the following message,

Starting up database 'msdb'.
Starting up database 'mssqlsystemresource'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
Starting up database 'model'.

I can clearly say that there is no E:\ partition on my machine. Can you please help me to fix the path which I assume is the error now?

Solution 1

I don't know if it is a bug or something that usually does SQL Server 2014. Some of the references to the databases, will remain like this:

E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf

So you basically have to alter them directly in the database.

Run the CMD Console as Administrator and access your database, like this:

SQLCMD -S .\

Or like this in case you have an instance:

SQLCMD –S .\INSTANCENAME

Rename the files to the default location with these sentences:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf');
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.mdf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.ldf');
GO

After that, your sa user will remain without password (It happened to me, not sure if it happens always), just log in again to the console and alter the password manually:

ALTER LOGIN sa WITH PASSWORD = 'newpassword' UNLOCK

When I did this last step, I had a problem, because my default login was configured with Windows Authentication, so the instruction:

SQLCMD -S .\

didn't work, just add the sa user without a password:

SQLCMD -S .\ -Usa

Hope it helps.

I found that solution here:

https://social.technet.microsoft.com/wiki/contents/articles/31786.sql-server-not-starting-after-fresh-installation.aspx

Solution 2

It might be windows related issue,Which does not have appropriate permission. Please take a look on here: http://blog.sqlauthority.com/2016/06/27/sql-server-fcbopen-failed-not-open-file-file-number-2-os-error-5access-denied/

Comments

[alcohol is evil]

Giving full control to user NT Service\MSSQLSERVER helped in my case.

[khalil]

for those who still cant login can just create a new account CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS GO ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK] GO

[Wasted_Coder]

I worked at this for a very long time, and couldn't get it right, basically I could start SQL with the first part, but after that SQLCMD couldn't connect using windows authentication. And if I used MSSMS the db engined tried to load it's resources db at that same bad location. My solution was to simply give the engine what it wants... created a RamDisk and that path and copied the system dbs to that location. Now I can use MSSMS and set the paths to the correct values.

[Andrea Antonangeli]

Happened to me exactly the same issue, but in SQL Server 2017. Solved thanks to you.