Enabling Performance Counters in SQL Server 2000

This is an interesting problem we had in the Newsgroup. There are specific case where SQL Server's counters were 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 Perforam Monitor to check if the counters appear
    (a)  In command line, execute "perfmon"
    (b)  Add and check if the counter appear

PS: Note this FAQ is based on the conversation in the sqlserver.setup newsgroup.