SQL Server Performance Monitor counters missing

Another day, another batch of servers to build and add to those being monitored in Redgate’s SQL Server monitor.

Reminder – these servers are being built in a consistent process, using a brace of PowerShell / azure scripts and a pre-prepared SQL Server configuration file.

Went to add to the SQL Server Monitor, and got an error: “Connection failed (Bad data)”. That doesn’t sound good. One nice feature about SQL Server Monitor is that you can see the full error log – in this case, we see:

Redgate Monitor Connection Failed bad data

Fortunately, the error message is nice and specific (although I’m not sure what the “#Rnv” Exception code means). Basically, Perfmon didn’t have all the counters that were required. I could see counters for SQLAgent, and for SSIS, but no sign of SQL Server.

To Google!

Pinal Dave had this post on missing Perfmon SQLServer counters, which shows very nicely the problem in PerfMon. I followed the steps he went through (in summary, checking sys.dm_os_performance_counters, the registry keys in HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance) and everything looked as it should be. I went through the unload/load process too, and that also didn’t fix the problem. Hmm.

A Technet article led me to try lodctr /R – a forced reload of *all* performance counters. That threw up an error along the lines of “Unable to rebuild performance counter – error 2”. Which wasn’t helpful either, but did manage to give me something else to look for, which directed me to an article on the Geeks With Blogs site, which led me to find a discrepancy between the “Last Counter” value in HKLM\Software\Microsoft\Windows NT\CurrentVersion\PerfLib and the last number (not the highest number – these things aren’t in numeric order…) in the multi-line registry key value Counter in HKLM\Software\Microsoft\Windows NT\CurrentVersion\Perflib\009. Fix the former with the value from the latter, run through the lodctr /R again, and then reboot – that seemed to do the job. Thankfully.

Back to Redgate SQL Monitor, back to the server, retry connection, and all is good.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , . Bookmark the permalink.

4 Responses to SQL Server Performance Monitor counters missing

  1. Dirk Hondong says:

    Hi Thomas,
    it seems that this is now fixed in version 5.0
    https://documentation.red-gate.com/display/SM5/SQL+Monitor+5.0+release+notes

    “SRP-9523: Fixed a situation which could cause monitoring to stop when missing performance counters are detected”

    Regards
    Dirk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s