I assume a number of times when the machine is booted up, I am most likely to hit the Management Studio to work on some script or the other. But sometimes strange things happen and I get all sorts of error. There might be tons of reasons why SQL Server is not able to start. This blog is a result of a quick consulting engagement with one of my clients where I faced OS error.
They contact me to solve a production down situation where SQL was not starting after moving the database files from C Drive to D Drive. They shared the error message as below
—————————
Services
—————————
Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.
—————————
OK
—————————
This is a very generic error. My first data which I always ask is to give me SQL Server ERRORLOG when SQL is not able to start.
SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
Here is what they shared with me
2016-06-14 06:28:06.15 spid4s Error: 17204, Severity: 16, State: 1.
2016-06-14 06:28:06.15 spid4s FCB::Open failed: Could not open file D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2016-06-14 06:28:06.15 spid4s Error: 5120, Severity: 16, State: 101.
2016-06-14 06:28:06.15 spid4s Unable to open the physical file “D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2016-06-14 06:28:06.15 spid4s SQL Server shutdown has been initiated
It means that SQL was shutting down because master database was not getting opened. You can also get a similar message in Event log which same error number.
Source: MSSQLSERVER
Date: 6/13/2015 2:24:39 PM
Event ID: 17204
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: MySQLServer.MyCorp.local
Description:
FCB::Open failed: Could not open file D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
Our problem is OS error: 5(Access is denied.).
Fix for Error FCB Open
This is a Windows related issue where SQL Server does not have appropriate permission to the folder that contains the master database file and hence the error. Now, what should be done? Since we are having access denied, we need to give access. Here are the steps:
Click on the file (shown in the error message), right click and select properties. Then from within the “security” tab, verify that that the account for the SQL Server service has full control to this file. In my client’s case it was NT Service\MSSQLServer so we have given full control to that.
Have you encountered a similar situation? What has been your troubleshooting steps? I would love to learn the same from you too.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
Now the question is what changed.
I am getting this error when trying to attach an MDF file from a crashed SQL instance. New instance is 2014. Crashed instance was 2012. The files seem to have the same permissions as new databases that are running.
Excellent tip. Thank you!
Hi, i have faced a similar problem. In ma case, we have Windows Server 2012 R2 over VMWARE, this server is part of a Always-on cluster. one night with one knowign why, the hard drive where the databases are located in the secondary node, was OFFLINE, when we realized we brough the DISK ONLINE, we tried to do fail over and we couldnt, the reason was becase, the disk in node 2 had lost some permissions, comparing with node1, we found that NETWORK SERVICE permissions, were missing in the path were the databases are. We provided the permissions and every thing start to work fine. The question here for us, is why the DISK were our databases was OFFLINE. Thanks.
Thank you for these instructions. They helped me troubleshoot an issue when moving to a new SAN vendor for my SQL cluster. I Robocopy’d the various databases and logs from old SAN disks to new, but the MSSQLSERVER account was missing from several key SQL folders as you suggested. Adding it in allowed me to bring my cluster back online without issues.
Thanks again for your great advice.
Best wishes.
I am acing the similar issue. I tried all the above steps but still I am unable to resove the issue. Please help.
Thanks for the information unfortunately it did not resolve my issue. When reviewing the logs I see that sql is trying to access the system databases to a file path that does not exist. It is trying to use an E drive which the server does not have. The mdf files are on D and ldf on L drives. Have you ever seen this issue before?
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
looks like someone has tried to replace the system database from templates folder https://sqlserver-help.com/2014/02/27/information-why-we-cant-use-system-database-files-from-template-folder-rather-than-rebuilding-system-databases/
Hi Pinal,
The resolutions steps given by you didn’t worked for me. From my side, i’ve given all necessary permissions at SQL Server and Drive Level to SQL Service Account.
Below is the error message i received.
2019-12-20 11:10:11.08 spid7s Error: 17204, Severity: 16, State: 1.
2019-12-20 11:10:11.08 spid7s FCB::Open failed: Could not open file for file number 2. OS error: 3(failed to retrieve text for this error. Reason: 15100).
2019-12-20 11:10:11.08 spid7s Error: 5120, Severity: 16, State: 101.
2019-12-20 11:10:11.08 spid7s Unable to open the physical file “”. Operating system error 3: “3(failed to retrieve text for this error. Reason: 15105)”.
Thank you! In my case, for some reason, the NETWORKSERVICE user was missing in the folder for the instance id. To find out what user needed to be granted permissions, I looked at SqlServer Configuration Manager, where all the services are listed and you can see the users that run each service.
Thank you so much Panel, it solved my problem!!! This issue happened to my dev server after applying bunch of window patches, now I need identify which one caused the problem.
Hi Dave,
Thank you for the article.
In my case, an installation of new instances of SQL Server crashed. Attempts to start the service from SQL Configuration failed, wiht an error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf
The computer does not have a folder d:\dbs\, with D being a DVD ROM.
Can I reset the location of the system databases? Where is the proper location of the system databases?
Regards,
Alexander
Had this error on SQL Server 2019. The parameter for mastlog.ldf was missing. Had to add the parameter via the registry as Configuration Manager does not allow adding -l parameters. I would guess it’s not allowing to add it as the assumption is it’s there already.