Have you ever encountered a situation where SQL Server fails to start? It can be frustrating and time-consuming to figure out the root cause of the issue. In this blog post, we will discuss a common startup issue and provide steps to troubleshoot and resolve it.
Recently, I had a consulting engagement with a client who faced a production down situation where SQL Server was not starting after moving the database files from the C Drive to the D Drive. The error message they received was:
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 error message is quite generic and doesn’t provide much information about the underlying cause. To investigate further, I asked the client to provide me with the SQL Server ERRORLOG when the startup failure occurred. The ERRORLOG contains valuable information that can help identify the issue.
Upon reviewing the ERRORLOG, I found the following entries:
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.
The key information in these entries is the “OS error: 5(Access is denied.)” message. This indicates that SQL Server does not have the appropriate permissions to access the folder that contains the master database file.
To resolve this issue, follow these steps:
- Right-click on the file mentioned in the error message and select “Properties”.
- Go to the “Security” tab and verify that the account for the SQL Server service has full control over this file.
- If the account is not listed or does not have full control, add it and grant the necessary permissions.
In my client’s case, the account was “NT Service\MSSQLServer”. After granting full control to this account, SQL Server was able to start successfully.
It’s important to note that this issue is specific to Windows and the permissions set on the file system. By ensuring that SQL Server has the necessary permissions, you can avoid startup failures caused by access denied errors.
Have you encountered a similar situation? What troubleshooting steps did you take? I would love to hear about your experiences and learn from them. Feel free to share your thoughts in the comments below.