SQL Server Performance Monitor Counters are gone

[DBAWinnipeg]

Anyone have any ideas/suggestions on finding out why the SQL Server Performance Monitor Counters are missing?

The environment is

2 Clustered Machines
Windows 2000 Advanced Server
SQL Server 2000 Advanced


All of the other Windows Performance Monitors are there but the SQL Server specific ones are missing.

Help




Thanks in advance!!!

Colin in da 'Peg

 

[JayKusch]

You may be looking at a predefined PerfMon template someone else created. Try creating a new one and see if the options are presented to you again.

Thanks

J. Kusch

 

[DBAWinnipeg]

I tried creating a new one with no success.

When I right click on "add counters" and then drop down "Performance Objects" I get none of my SQL Server performance monitor counters.

Any other ideas?


Thanks in advance!!!

Colin in da 'Peg

 

[grimwyre]

We have the same problem, believed to be an intermittent problem on SQL SP3, although thare are other reasons. No definite solution yet. One possibility is to stop the Performance Counter service on the passive node, set it to manual start, then failover to that node. The counters might then be available. We haven't managed to test this yet. Then set the Perf Counter service on the new passive node the same, so if an unexpected failover occurs, the counters will be availble.

 

[DBAWinnipeg]

Want to flip a coin on who is going to try this?

The shop I work for is a 24/7 banking/loan company and although we do have a "weekly window" for maintenance it's never a popular solution

Are you just missing your SQL performance counters? or all counters?

We're just missing the SQL ones.



Thanks in advance!!!

Colin in da 'Peg

 

[grimwyre]

The SQL counters have gone, others seem Ok.

We have the same problem about outage. We are trying to arrange a test, will post results if it happens.

 

[JayKusch]

OK ... is the account you are running on have Admin rights. If not, you will not be able to see the SQL Server specific counters. By default the SS counters are added to PerfMon.

Thanks

J. Kusch

 

[JayKusch]

How about going down this list of steps to check:

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




Thanks

J. Kusch

 

[DBAWinnipeg]

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


none of these exist

Should I create them or does step 4 and 5 (Unloading and Reloading the SQL Server Counters) take care of this?



Thanks in advance!!!

Colin in da 'Peg

 

[JayKusch]

I beleive the Unload/Reload takes care of this. Has been a while since I last had to do this.

SO ... are you running under an Admin account? Just Checkin' - LOL

Thanks

J. Kusch

 

[DBAWinnipeg]

Yes the psadmin account that is being logged in is an Admin account.

Thanks

and this being a 24/7 operation it'll be a few days before I get my "window of opportunity" but I'll definetly let you know if it worked or not

Thanks again!!

 

[MDXer]

do you get any errors in your SQL log when you try to load performance counters. I had a similiar problem that reported.

Performance monitor shared memory setup failed: -1

this actually helped in diagnosing the problem.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[grimwyre]

We are now running with the PerfMon service stopped on the passive node, and set to Manual startup. Failovers were done recently and our counters are still present. Because of the erratic type of fail, we can't say this is a fix, but if it reduces the problem, it is low cost and low risk.

We were also getting "Performance monitor shared memory setup failed: -1" in our SQL error log when the counters were lost.