INF: How to Debug "Service State Indeterminate" in SEM

• Microsoft SQL Server, version 6.0

SUMMARY

After registering a SQL Server successfully to SQL Enterprise Manager (SEM), you may not be able to control the SQL Server service using the service control "stop light" under certain situations. If you click on the "stop light" button on the toolbar, the "stop light" dialog box will return the message "service state indeterminate." In this case, the server status icon "stop light" will not light, and the SQL Executive icon will not change color to reflect the status either. Also, you will not be able to control the services such as starting and pausing the SQL Server from within the SEM. This article discusses when and why this situation may occur.

MORE INFORMATION

When registering a SQL Server, you are actually performing two tasks:

1. Connecting to the SQL Server using whatever IPC (Inter-Process Communication mechanism) that is configured as the default; and

   connecting.

Microsoft SQL Enterprise Manager (SEM) controls and monitors SQL Server and SQL Executive status through the Windows NT service control APIs. When you use the SEM "stop light" to start SQL Server, for example, the SEM will call the Windows NT service control functions to control the SQL Server service. If the SQL Server and SEM are on two separate computers, the service control is carried out through Windows NT Remote Procedure Calls (RPCs), which locate the SQL Server computer using the name you provide when registering the SQL Server.

There are situations when this service control may not work:

1. Running SEM under Windows 95. Although you can connect to SQL Server from SEM running under Windows 95, you can not control (for example, start, pause) the service remotely because Windows 95 does not support Windows NT service control APIs.

2. Registering SQL Server using a name different from the Windows NT server name. If you define an alias using the Advanced option in SQL Client Configuration Utility, you can connect to the SQL Server using this alias, but service control will be unavailable because the Windows NT service control will try to locate the server using the alias as the server name, which does not exist of course. This is true even if the SEM and SQL Server reside on the same computer.

   One common scenario is that you are running both SQL Server 4.21 and 6.0 on the same machine. Because you can not use the same server name to connect to both SQL Servers, one of them must be connected using an alias which is different from the Windows NT server name. In this case, the SQL Server that is registered using an alias will have the service control disabled.

3. Windows NT service control or RPC not available. If the Windows NT service control is not available for some reason, such as permissions problems or RPC not available, the service control from SEM will be unavailable also. To verify whether you have any problems with the Windows NT service control, you can use the Windows NT Server Manager to connect to the SQL Server computer, and try to remotely start or stop some services such as the SQL Server service. If this attempt fails, it indicates you either do not have the permissions, or the Windows NT service control is not functioning properly and you should contact your primary Windows NT support provider for further help.