Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. "Could not find stored procedure sp_server_diagnostics" error and the instances of SQL Server 2008 R2 or SQL Server 2008 cannot fail over in a SQL Server 2012 cluster environment

"Could not find stored procedure sp_server_diagnostics" error and the instances of SQL Server 2008 R2 or SQL Server 2008 cannot fail over in a SQL Server 2012 cluster environment

View products that this article applies to.

Symptoms

Consider the following scenario:
  • You install a Microsoft SQL Server 2012 failover cluster instance with two nodes (assume that the names of the nodes are Node 1 and Node 2).
  • You install a new failover cluster instance of Microsoft SQL Server 2008 R2 or a new failover cluster instance of Microsoft SQL Server 2008 on Node 2.
  • You uset he "Add Node" operation in order to add Node 1 to the failover cluster instance of SQL Server 2008 or SQL Server 2008 R2 that you installed on Node 2.
  • You try to failover the failover cluster instance of SQL Server 2008 or SQL Server 2008 R2 from Node 2 to Node 1.

In this scenario, the failover cluster instance of SQL Server 2008 or SQL Server 2008 R2 stays in the "Pending Online" state on Node 1, and then it does not come online on Node 1. Finally, this failover cluster instance switches back to Node 2. Additionally, error messages that resemble the following are logged in the cluster logs:

00000e14.00000c6c::<Time stamp> INFO [RES] SQL Server <SQL Server Instance>: [sqsrvres] Connect to SQL Server ...
00000e14.00000c6c::<Time stamp> INFO [RES] SQL Server <SQL Server Instance>: [sqsrvres] The connection was established successfully
00000e14.00000c6c::<Time stamp> INFO [RES] SQL Server <SQL Server Instance>: [sqsrvres] Run 'EXEC sp_server_diagnostics 20' returns following information
00000e14.00000c6c::<Time stamp> ERR [RES] SQL Server <SQL Server Instance>: [sqsrvres] ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'sp_server_diagnostics'. (2812)
00000e14.00000c6c::<Time stamp> ERR [RES] SQL Server <SQL Server Instance>: [sqsrvres] Failed to run diagnostics command. See previous log for error message
00000e14.00000c6c::<Time stamp> INFO [RES] SQL Server <SQL Server Instance>: [sqsrvres] Disconnect from SQL Server
00000e14.000006fc::<Time stamp> ERR [RHS] RhsCall::DeadlockMonitor: Call ONLINERESOURCE timed out for resource '<SQL Server Instance>'.
00000e14.000006fc::<Time stamp> ERR [RHS] Resource <SQL Server Instance> handling deadlock. Cleaning current operation.
00000d78.00000740::<Time stamp> WARN [RCM] HandleMonitorReply: FAILURENOTIFICATION for '<SQL Server Instance>', gen(1) result 5018.
00000d78.00000740::<Time stamp> INFO [RCM] TransitionToState(<SQL Server Instance>) OnlinePending-->ProcessingFailure.

Note The impact of this issue is that the instance of either SQL Server 2008 R2 or SQL Server 2008 only runs on the node that it is installed on, and the instance does not have high availability as it cannot fail over to other nodes.

↑ Back to the top

Cause

The issue occurs because of the sequence in which registry entries for the SQL Server 2008 R2 or SQL Server 2008 instance are written by the Setup program and queried by the Resource Health Service component (RHS.exe) of the cluster service. This problem occurs only when there is an existing instance of a later version of SQL Server already installed on the cluster. For more information, check the "More Information" section in this article.

↑ Back to the top

Workaround

To work around this issue, after the "Add Node" operation, perform either of the following steps on each node of the cluster that has to be designated as a possible owner for the SQL Server 2008 R2 or SQL Server 2008 instance:
  • Stop and restart the cluster service.
  • Restart the node.
Notes
  • Either of these actions will cause all the active resources on the node to fail over to one of the other nodes on the cluster. Therefore, you must schedule the action during a downtime.
  • If you have not yet installed the SQL Server 2008 R2 or the SQL Server 2008 instance, in order to prevent this issue, you can install the instance by using the Advanced cluster preparation and Advanced cluster completion procedures. For more information about these procedures, go and see the following article in SQL Server Books Online:
    How to create a new SQL Server failover cluster
    The following is a screen shot of these options from the installation page:A screen shot for the

↑ Back to the top

More Information

The following sequence of events occurs when you install a clustered instance of SQL Server on any node:
  1. To monitor the health of the resource, the cluster service instantiates an instance of Resource Host Subsystem (RHS.exe) for that instance on all the nodes of the cluster.
  2. The RHS.exe for SQL server uses the Sqlserver resource DLL file (sqlsrvres.dll) to monitor the health of the SQL instance. This program always uses the latest version of sqlsrvres.dll that is found on the node for this purpose.
  3. Sqlsrvres.dll queries the registry for information about SQL Server resources and uses it to check the health of the service.

Note These steps occur even before you run the "Add Node" operation.

If there was no instance of SQL Server on the passive nodes, there will be no copy of sqlsrvres.dll on the nodes. Therefore, the RHS.exe would have been exited as it has no work to do.

However, in the scenario that is covered in the "Symptoms" section, the following events occur on the remaining nodes of the cluster where you have not installed the instance for SQL Server 2008 R2 or SQL Server 2008:
  1. The instance of RHS.exe for the down-level version of SQL Server finds the SQL Server 2012 version of sqlsrvres.dll during instantiation. The SQL Server 2012 version of sqlsrvres.dll can detect both SQL Server 2012 and SQL Server 2008 R2 or SQL Server 2008 instances.
    • For the instance of SQL Server 2012, it issues the sp_server_diagnostics command.
    • For the instance of SQL Server 2008 or SQL Server 2008 R2, it issues the following command:
      SELECT @@SERVERNAME

    The default assumption is that the resource belongs to SQL Server 2012 version.
  2. The instance of RHS.exe then queries the private property of the SQL Server resource from the following cluster registry key in order to obtain the instance names of the SQL server:
    HKEY_LOCAL_MACHINE\Cluster\Resources\< Resource GUID >\Parameters
  3. As soon as RHS.exe obtains the instance name, it looks up the following instance-specific registry key to obtain the version number:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL10_50.< Instance name >\Setup
  4. Because these registry keys for the down-level version are not yet populated on the nodes other than the one where the SQL Server 2008 R2 or SQL Server 2008 instance is installed, this instance of RHS.exe continues to run while assuming that the instance it has to monitor is a SQL Server 2012 instance (therefore, use sp_server_diagnostics for health checks).
  5. After you run the "Add Node" operation to add a new node to the SQL Server 2008 R2 or SQL Server 2008 instance, although the registry keys for the down level instance are now populated, because RHS.exe is not restarted, when the instance for SQL Server tries to come online on this newly added node, it continues to use the sp_server_diagnostics command for checking the state of the SQL Server 2008 R2 or SQL Server 2008 instance.
  6. Because this stored procedure is new in SQL Server 2012 and does not exist in the older versions of SQL Server, the query fails. Therefore, the resource cannot come online on these nodes, and you receive the error message that is mentioned in the "Symptoms" section.
If you use the Advanced cluster preparation and Advanced cluster completion procedures, the required registry keys are created before the cluster resources are created. Therefore, you do not experience this problem.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

Keywords: kb, kbsurveynew, kbtshoot, kbexpertiseadvanced

↑ Back to the top

Article Info
Article ID : 2938136
Revision : 1
Created on : 1/7/2017
Published on : 3/12/2014
Exists online : False
Views : 381