Adding Performance Counters back for SQL Server

I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account.

Next, I went to add an alert, and I only had the XTP counters.

2017-06-14 12_16_20-SQLQuery4.sql - (local)_SQL2016.sandbox2 (PLATO_Steve (63))_ - Microsoft SQL Ser

The counters are also missing in Performance Monitor. What is interesting is that I show the correct SQLAgent counters for each of my three instances.

2017-06-16 08_54_19-Add Counters

A quick search found me this blog on MSDN, where it recommends the following:

unlodctr mssqlserver

lodctr perf-mssqlserversqlctr.ini

I had a named instance, so for me I entered:

unlodctr mssql$sql2016

lodctr perfMSSQL$SQL2016sqlctr.ini

from an elevated command prompt. Running the last command again shows the counters loaded.

2017-06-16 09_19_26-cmd (Admin)

I also checked my registry, which appeared to be fine:

2017-06-16 09_08_38-Adding Performance Counters back for SQL Server - Open Live Writer

I next found another blog that noted I might need to resynch WMI, so I ran winmgmt, using the PID from Task Explorer (details tab):

2017-06-16 09_24_32-cmd (Admin)

I didn’t see counters at first, but I restarted the instance. Once that was done …

2017-06-16 09_23_29-New Alert

A nice fix, and one I probably won’t forget after this blog.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.