One of the servers which was built a long time ago and it has SQL Services running in a Clustered Environment. It was decided that this Server be put into use.
When the DBA Team tried to bring the SQL Resource ONLINE in Cluster Administrator, it kept on failing. The first place to look for the error was the Cluster.log file located on C:\WINDOWS\CLUSTER. Wherever there was an attempt to bring the SQL Services online, there was an entry in the Cluster log which said “The system cannot find the file specified”. Nothing more nothing less !
So by practice, headed straight back to Cluster Administrator, to check if any of the Disk Resources had failed. Hard luck, all disk resources were Online.
Next place to look for was the System Event Log. Even there found the same mystery message “The system cannot find the file specified”. In order to see if this is happening after the SQL Services came up but before the databases came online, checked the folder on which the SQL Server Error Log file is located. But on that folder the Error Log was not created at the time when the SQL Resources tried to come ONLINE.
Even though the SQL Services were running on a Cluster, in order to fix this issue opened up the Services Console (services.msc). Tried to start the service which was in “Manual” mode and guess what, same error.
So as the next course of action, opened up Command Prompt. CD’ed to the directory where the binaries of SQL Server were located and fired up the below command.
sqlservr.exe -c -m
As per Books Online the definitions of above arguments of sqlservr.exe are as below,
Indicates that an instance of SQL Server is started independently of the Windows NT Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start. (Note that when you use this option, you cannot stop SQL Server by using SQL Server Service Manager or the net stop command, and if you log off the Microsoft Windows NT® system, SQL Server will be stopped.)
Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode. The CHECKPOINT mechanism, which guarantees that completed transactions are regularly written from the disk cache to the database device, is not started. (Typically, this option is used if you experience problems with system databases that require repair.)
When SQL Server was started in Single User Mode, it started normally!
This clearly indicated that none of the files related to SQL Server were missing and the error message points to somewhere else.
So again headed back to services.msc and checked the properties of SQL Server Service. The path of the executable “sqlserver.exe” looked a little wierd as in DOS ages.
Since the service is already down, there is no harm in trying. So opened Registry Editor (regedit.exe) and navigated to
and edited the value of ImagePath to the absolute path of sqlservr.exe, i.e. C:\PROGRAM Files\MICROSOFT SQL SERVER\MSSQL\binn\sqlservr.exe
Now tried to start the SQL Server Service and it started ! After stopping it opened Cluster Administrator and brought the SQL Resources online.
Even though this not the most elegant way of handling this issue, but somehow it was needed to fix the issue.