Microsoft KB Archive/227662

= PRB: SQL Performance Monitor Counters Missing =

Article ID: 227662

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q227662



IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



SYMPTOMS
When you use the SQL Performance Monitor, some or all of the SQL counters are missing. In addition, one or more of the following error messages may be logged into the application event log:

Source: SQLCTR60

Event Id: 7

Description: DB-LIBRARY - SQL Server message: Login failed

Source: SQLCTR60

Event Id: 8

Description: DB-LIBRARY error - Login incorrect

Source: SQLCTR60

Event Id: 2

Description: OpenSQLPerformanceData: Cannot Connect to SQL Server - [ServerName]

Source: SQLCTR60

Event Id: 9

Description: CollectSQLPerformanceData: NULL dbProc Encountered

Source: SQLCTR60

Event Id: 10

Description: CollectSQLPerformanceData: Connection lost, attempting to reconnect to SQL Server [ServerName]

Source: Perflib

Event Id: 1011

Description: The library file "" specified for the "MSSQLServer" service could not be opened. Performance data for this service will not be available. Status code is data DWORD 0.



CAUSE
Typically, the counters are missing due to one of the following causes:
 * Problems with the initial setup of the SQL Performance Monitor.
 * Problems with the login that the SQL Performance Monitor uses.
 * Problems with the registry.
 * Problems with the files that the SQL Performance Monitor uses.



Problems with Performance Monitor Setup
 Make sure that SQL Performance Monitor integration is on.

 Run the SQL Server setup on the SQL Server. Click Continue. Click Continue. Click Set Server Options. Click Continue.</li> Make sure that the SQL Perfmon Integration box is selected.</li> Click Change Options.</li> After you change the options, click Exit to return to Microsoft Windows NT.</li> Stop and restart the SQL Server.</li></ol>

NOTE: If SQL Perfmon Integration is already selected, try clearing it, changing the options, exiting setup, and then perform the preceding steps.</li>  Make sure the stored procedure MS_sqlctrs_users exists in the master database. If not, use ISQL/w to run the following script (Note that the script Procsyst.sql located in the \MSSQL\Install folder also contains this code): if exists (select * from sysobjects where id = object_id('dbo.MS_sqlctrs_users') and sysstat & 0xf = 4) drop procedure dbo.MS_sqlctrs_users GO create procedure MS_sqlctrs_users as select syslogins.name+' - '+convert(varchar(30), sysprocesses.spid), memusage 'Memory (2K Pages)', cpu 'CPU time', physical_io, count(syslocks.spid) 'Locks held',sysprocesses.spid from sysprocesses, syslocks, syslogins where sysprocesses.spid *= syslocks.spid and sysprocesses.suid=syslogins.suid group by syslogins.name,sysprocesses.spid,memusage,cpu,physical_io GO GRANT EXECUTE  ON dbo.MS_sqlctrs_users TO public GO </li> Use Regedt32.exe to check the following registry key:

If this key exists and if the value is 0x1, Performance Monitor is being forced to make a trusted connection to SQL Server. In this case, use Enterprise Manager to make sure that the server is listening on either named pipes or multiprotocol:

<ol style="list-style-type: lower-alpha;"> Run SQL Setup on the SQL Server.</li> Click Continue.</li> Click Continue.</li> Choose Change Network Support.</li> Click Continue.</li> Make sure an X exists in the box beside either Named Pipes or Multi-Protocol.</li> Click OK.</li> Accept the defaults and click Continue.</li> Click Exit to return to Windows NT after the options are changed.</li> <li>Stop and restart SQL Server.</li></ol> </li></ol>

Problems with the Performance Monitor Login
NOTE: The information in this login troubleshooting section does not apply when Performance Monitor makes a trusted connection to the server, which happens when:


 * The server is using integrated security.

-or-
 * The registry has been modified so that it forces Performance Monitor to make a trusted connection. For further information on forcing the trusted connection, please see SQL Server Books Online.

<ol> <li> Make sure that the probe login exists by running the following query in ISQL/w: sp_helplogins probe If the login does not exist, use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to create the login with the correct permissions. </li> <li>Make sure that the probe login has no password by performing the following steps in Enterprise Manager: <ol style="list-style-type: lower-alpha;"> <li>Double-click probe under the logins folder.</li> <li>Clear the Password box.</li> <li>Click Modify.</li> <li>Click OK, without adding anything to the Confirm New Password Box.</li> <li>Click Close.</li></ol> </li> <li> Make sure that the default database for probe is the master database by issuing the following query from ISQL/w: sp_defaultdb probe, master </li> <li>If the default permissions for the public group or for the probe login have changed, use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to give correct permissions to the probe login.</li> <li> Make sure that the server user ID (SUID) for the probe login matches the SUID for the probe user in the master database by issuing the following from ISQL/w: select sl.name, sl.suid 'syslogins suid', su.suid 'sysusers suid' from master..syslogins sl, master..sysusers su where sl.name = 'probe' and sl.suid = su.suid If not, delete both the probe user and the probe login, then use ISQL/w to run the Procsyst.sql script located in the \MSSQL\Install folder to create the probe login and user with the correct permissions. </li></ol>

Problems with the Registry
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

<ol> <li>Use Regedt32.exe to check the following registry key:

<ol style="list-style-type: lower-alpha;"> <li>Make sure that this key exists. If it does not, rebuild the registry by running the following in the MSSQL\BINN folder from a command prompt: setup /t RegistryRebuild = On NOTE: Microsoft recommends that you make a complete backup of your system before you perform the registry rebuild.</li> <li>Make sure that the value for this key is Sqlctr60.dll. If not, use the registry editor to modify the key to the correct value.</li> <li>Make sure that the permissions are correct on the key. In the registry editor, highlight the performance folder, and then from the top menu, click Security and then select Permissions. Make sure that the following users have the permissions indicated:</li></ol>

<ul> <li>Administrators - Full Control</li> <li>Everyone - Special Access</li> <li>SYSTEM - Full Control</li></ul> </li> <li>Use Regedt32.exe to check the following registry key:

<ol style="list-style-type: lower-alpha;"> <li>Make sure that this key contains all the SQL Performance Monitor counters. If not, rebuild the registry by using the preceding steps.</li> <li>Make sure the 009 folder has the following permissions: <ul> <li>CREATOR OWNER - Full control</li> <li>Administrators - Full Control</li> <li>Everyone - Read</li> <li>SYSTEM - Full Control</li></ul> </li></ol> </li> <li>Use Regedt32.exe to check the following registry keys:

Make sure that the user who is trying to monitor SQL Server with Performance Monitor has at least READ permission on these keys.</li></ol>

Problems with Performance Monitor Files
<ol> <li>Make sure that the system path variable includes the path to the Sqlctr60.dll file.

<ol style="list-style-type: lower-alpha;"> <li>In Control Panel select System, and then click the Environment tab.</li> <li>Under System Variables, highlight Path.</li> <li>If the Value box does not contains the path to Sqlctr60.dll (by default, the path to the file is \MSSQL\BINN), add the path, preceded to the end of the string. Note that each path entered must be separated by a semi-colon .</li> <li>Click Set.</li> <li>Click OK.</li></ol> </li> <li>If all else fails, it is possible that the Sqlctr60.dll file is the incorrect version or that it is damaged. In this case, copy the file from the SQL Server 6.5 CD. Note that this file has been changed in Service Packs 5 and 5a, so if you are running either of these two Service Packs, the file should be copied from the Service Pack instead of the CD.</li> <li>If counters still do not show and the computer is a Windows NT 4.0-based computer, it is possible that the Windows NT performance data files, Perfc009.dat and Perfh009.dat, are damaged. In this case, they must be replaced from the Windows NT 4.0 CD by using the following steps:

<ol style="list-style-type: lower-alpha;"> <li>In the \Winnt\System32 folder, rename the following files:

Perfc009.dat

Perfh009.dat

For more information, please see the following article in the Microsoft Knowledge Base:

127207 Missing Objects and Counters in Performance Monitor

NOTE: All files that contain "009" are used when the default language is English. The preceding file list is referring to English files. Other languages will have different numbers.

</li> <li>Copy the files Expand.exe, Perfc009.da_, and Perfh009.da_ from the Windows NT 4.0 CD to the \Winnt\System32 folder on the local computer.</li> <li>From a command prompt in the \Winnt\System32 folder, expand the Perfc009.da_ file by running the following: Expand.exe Perfc009.da_ Perfc009.dat </li> <li>From a command prompt in the \Winnt\System32 folder, expand the Perfh009.da_ file by running the following:

Expand.exe Perfh009.da_ Perfh009.dat </li> <li>Stop SQL Server.</li> <li>Using SQL Setup, turn performance monitor integration off by clearing the box beside SQL Perfmon Integration. (Please see step 1 in the Problems with Performance Monitor Setup section). Exit setup after the option changes.</li> <li>Using SQL Setup, turn performance monitor integration on by selecting the box beside SQL Perfmon Integration. (Please see step 1 in the Problems with Performance Monitor Setup section). Exit setup after the option changes.</li> <li>Start SQL Server.</li></ol> </li></ol>

SQL Server Performance Counters on Clustered SQL Servers
SQL Server Performance Monitor counters for the Virtual Server are not present when MicrosoftSQL Server 7.0 is set up with a virtual SQL Server configuration and the passive node has control of the resources. The counters are not available to the primary node until you shutdown and restart the whole cluster. Even then, availability is sporadic.

The SQL Server extension counters must be found when the system initially starts. Because the counters DLL file is located in the \Mssql\Binn folder for SQL Server 6.5 by default, the counters are not found when the initial system startup takes place. This occurs because the cluster drive in which SQL Server is installed is not accessible until the MSCS resources are all online. SQL Server 7.0 already places these counters in the proper folder, %Systemroot%\System32\ so that they are available. To make the Sqlctr65.dll file available, place a copy of the Sqlctr65.dll file in the %Systemroot%\System32 folder. The Sqlctr70.dll file is placed in this folder by default.

For additional information about SQL Server Performance Counters, click the article numbers below to view the articles in the Microsoft Knowledge Base:

127207 Missing Objects and Counters in Performance Monitor

246328 PRB: SQL Performance Counters May be Missing After MDAC Installation on a Cluster

WARNING: If you use the following article, read it and compare it to other sections in the following Microsoft Knowledge Base article, which discusses rebuilding the SQL Server registry entries.

227662 PRB: SQL Performance Monitor Counters Missing

Performance counters on clustered SQL Servers are not always available. When performance counters are available, they are usually only on the primary node if no failover has occurred.

SQL 6.5 Performance Counters with a Named SQL 2000 Instance
If SQL Server 2000 is installed as a named instance on the same computer as SQL Server 6.5, and the SQL Server 6.5 computer is not configured to listen on the TCP/IP Sockets network library, performance monitor counters for SQL 6.5 will not be available. By default, SQL Server 2000 will first attempt to make a connection to a server using sockets. The following two workarounds may be used:
 * Configure SQL Server 6.5 to listen on the TCP/IP Sockets network library using port 1433. This can be done by using the SQL Setup program or SQL Enterprise Manager. SQL Server must be restarted after making this change before the counters will be available.
 * Using the SQL 2000 Client Network Utility, change Named Pipes to be the first entry in the Enabled Protocols by Order list box.