Sometimes there are various issues with the operating system and disk which would cause an issue with SQL Server. In this blog, I am going to share a recent troubleshooting done for one of my clients. Let us see the solution to fix the issue of SQL Service not starting in a cluster.
My client contacted me and informed that they are not able to perform failover of SQL Server Clustered instance from Node1 to Node2. Here is the error message which they saw in Node2 system event log when they were trying to failover SQL Server from Node1 to Nodde2
Log Name: System
Source: Service Control Manager
Date: 1/28/2018 3:21:30 PM
Event ID: 7000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: Node2
Description:
The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The system cannot find the file specified.
I asked them to check the path of sqlservr.exe in services.msc as shown below. This can be done by Right Click on SQL Service and go to properties.
The path was H:\MSSQL\MSSQL14.MSSQLSERVER\MSSQL\Binn\
Strangely, there was no file in the path which was found above. By default, the binaries in the clustered instance are installed on local disk which was H drive in their environment. When we discussed further, they informed that they had some issue with the drive and their storage team assisted them to replace the drive. They were under the impression that all files have been copied from old drive to new drive but that was not the case.
WORKAROUND/SOLUTION
Since we know that the cause of the issue was missing binary and executables from the local node, there were two feasible options to fix the issue which I can think of. We can either run “Repair” of the instance or we can perform “RemoveNode” followed by “AddNode”.
We went ahead and followed RemoveNode and then AddNode. Once completed, we were able to failover SQL Server to Node2 without any problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)
