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:
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.
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.