Missing SQL PerfMon counters

A server has just lost all the SQL Server PerfMon counters. They’ve all disappeared into thin air. It plays havoc with our monitoring tool! We’ve had a couple of instances of this lately. I haven’t found the cause and it’s starting to get on my nerves. Luckily the answer is fairly simple once you know. NB: This involves restarting the SQL Server service so you might want to do it out of hours.

The steps are:

  1. Run cmd.exe as Administrator.
  2. Go to the Binn directory. On my SQL 2014 instance this was cd C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn.
  3. unlodctr MSSQLSERVER (or unlodctr MSSQLSERVER$INSTANCENAME for a named instance).
  4. lodctr perf-MSSQLSERVERsqlctr.ini (or lodctr perf-MSSQLSERVER$INSTANCENAMEsqlctr.ini for a named instance).
  5. Restart the Remote Registry service if running.
  6. Restart the Performance Logs & Alerts service if running.
  7. Restart the SQL Server service.

There are some other strange things that can happen: Remote Registry service stopped, all PerfMon counters – or just SQL counters – disabled in the Registry.

Resources:

http://www.travisgan.com/2013/05/missing-sql-performance-counters.html

http://blog.sqlauthority.com/2014/11/07/sql-server-performance-counter-missing-how-to-get-them-back/

http://blogs.technet.com/b/pfelatam/archive/2011/08/08/sql-performance-counters-are-missing.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/18/what-should-we-do-when-the-sql-server-performance-counters-are-missing.aspx

http://blogs.msdn.com/b/psssql/archive/2009/03/03/troubleshooting-sql-server-2005-2008-performance-counter-collection-problems.aspx

Advertisements

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