Adding Performance Counters back for SQL Server

Steve Jones, 2017-06-26

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.

Filed under: Blog Tagged: administration, sql server, syndicated





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads