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
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.
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.
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.
Date: 6/13/2015 2:24:39 PM
Event ID: 17204
Task Category: Server
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)