Unable to monitor MSSQL via WMI – SQL Advanced Metrics

by May 12, 2017

The SQL Advanced Metrics service monitor is unable to pull counters from WMI…

This can occur from time to time with any product that publishes performance counters to WMI. For SQL specifically, we will usually need to reload counters. Sometimes, the WMI service just loses the counters and a simple restart of WMI and the dependent services can fix it. Below is a script that will shut down the appropriate services, including SQL server, so BE WARNED this can be very BAD to run on a production SQL server during regular hours so maintenance should be scheduled. You MAY try running the rest of the script without the MSSQL server shutdown and this may fix the issue. The problem can stem from multiple sources (hard to track down) so this script is a "cure all" for the issue. The script contents is below for those wanting to execute the steps manually.

NOTE: the directory depends on your version of MSSQL server, the one shown is for a SQL 2014 instance.

Solution

Open an administrative command prompt and execute:

cd c:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinn
net stop "Remote Registry"
net stop "Performance Logs & Alerts"
net stop "User Access Logging Service"
net stop "IP Helper"
net stop "Windows Management Instrumentation"
net stop MSSQLSERVER
run "unlodctr MSSQLSERVER"
run "lodctr perf-MSSQLSERVERsqlctr.ini"
net start "Remote Registry"
net start "Performance Logs & Alerts"
net stop "Windows Management Instrumentation"
net start "User Access Logging Service"
net start "IP Helper"
net start MSSQLSERVER

After executing, simply test the SQL Advanced Metrics service monitor again. Metrics should now be restored.