While preparing a lab for my upcoming training session, I was trying to play with the cluster. While doing that I was in a situation where I was unable to start SQL Service.
I looked into SQL Server ERRORLOG to see the cause of SQL Startup failure. Here were the last messages logged in the ERRORLOG. If you are new to SQL Server then refer below blog to find the location of the ERRORLOG file. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
2018-01-28 15:29:54.27 Server SNIInitialize() failed with error 0x2.
2018-01-28 15:29:54.27 Server SQL Server shutdown has been initiated
The error code mentioned is 0x2 which is 2 in decimal. Error Number 2 in windows means “The system cannot find the file specified.” Interesting, same error appears when I try to start SQL Service using services.msc.
Now, to find what is missing, I captured registry access while starting SQL Service using Process Monitor tool.
As we can see above, SQL Server was looking for a key called “ClusterName” in the registry and that is missing. Once we see “NAME NOT FOUND” then we see two “WriteFile” in SQL Server ERRORLOG followed by a “CloseFile” for ERRORLOG. This confirms that this is the right reason for SQL Startup failure. In my case, there was no SQL cluster and I was just trying to fool SQL Server by showing it as cluster but looks like there are more keys needed.
Below is the registry key which should not be there if SQL is standalone. So we should take a backup and delete the “Cluster” key. Here is the complete path for my instance in case the image is not clear.
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Cluster
The path would change based on SQL Server version and instance name. Here is the list of SQL versions (relevant as of today) and the respective keys in the registry.
|SQL Server 2008||MSSQL10|
|SQL Server 2008 R2||MSSQL10_50|
|SQL Server 2012||MSSQL11|
|SQL Server 2014||MSSQL12|
|SQL Server 2016||MSSQL13|
|SQL Server 2017||MSSQL14|
We need to append Instance Name (MSSQLSERVER for default instance) to make the complete registry key. By going with about logic, for my server SQL 2017, default instance it is MSSQL14.MSSQLSERVER.
Hope this blog helps someone who faces the similar issue.
Reference: Pinal Dave (https://blog.sqlauthority.com)