Article: 2009-001
Product: SNMP Informant - SQL
Subject: Missing SQL 2000 Performance Counters

Keywords: SQL 2000, performance counters, missing

Last Updated: August 13, 2009

This article is intended to help SNMP Informant users (re)activate SQL Server 2000 performance counters.  If the SQL Performance objects are missing, then SNMP Informant cannot access them, and they will NOT be available using SNMP.

Enabling Performance Counters in SQL Server 2000

There are specific cases where SQL Server's counters could be missing. In this FAQ we assume that the installation of SQL Server 2000 is a default installation.

There are many reasons when the SQL Server counters will disappear, such as Permission, Server Crash, Registry Modification and Virus. To work around the symptom, please take the following actions:

1. Check if you have full permission to access the local folder: C:\Program Files\Microsoft SQL Server\MSSQL\  Make sure that you can access this folder or your current account is located in local administrator group.

2. Make sure there is no third-party application which add keys in Register to disable the performance counters.You can open the registry to check if there is a key named "Disable Performance Counters" valued "1" in:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]
If it exists, please delete it.

3. Under the register key:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]
Make sure the following keys with the value DO EXIST. If not, please create a new one and add the value.

# Note # : The "Last Counter", "Last Help", "First Help", "WbemAdapFileSignature", "WbemAdapFileTime", and "First Counter" values can vary between SQL Server installations.

"Library"=" C:\PROGRA~1\MICROS~3\MSSQL$~1\BINN\SQLCTR80.DLL "
"Collect"="CollectSQLPerformanceData"
"Open"="OpenSQLPerformanceData"
"Close"="CloseSQLPerformanceData"
"Last Counter"=dword:000016ce
"Last Help"=dword:000016cf
"First Help"=dword:000015bb
"WbemAdapFileSignature"=hex:d1,70,dc,f8,a7,75,5e,e4,9e,e6,dd,91,9e,cd,06,65
"WbemAdapFileTime"=hex:d6,34,9d,95,64,0d,c2,01
"WbemAdapFileSize"=dword:00008238
"WbemAdapStatus"=dword:00000000
"First Counter"=dword:000015ba

4. Unload the SQL Server Counters. In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)

5. Reloading the SQL Server Counters
     (a) In command line, locate the current folder to C:\Program Files\Microsoft SQL Server\MSSQL\BINN
     (b) Execute "lodctr sqlctr.ini"

6. Stop SQL Server Services and Restart the Services
    (a) In command line, execute "net stop mssqlserver"
    (b) Execute "net start mssqlserver"
    (Or you can perform it in Service Manager or in System Services)

7. Open Performance Monitor to check if the counters appear
    (a)  In command line, execute "perfmon"
    (b)  Add and check if the counters appear