I see no SQL Server 2008 Counters in Perfmon?

This problem is a real pain and I’m yet to determine why this issue occurs because my understanding is SQL Server counters are automatically installed as part of SQL Server installation. Let’s pretend you have recently installed SQL Server 2008 R2 instance on one of our company server. After the installation you noticed that SQL Server 2008 counters are not installed as part of SQL Server 2008 installation.

Now if you ever encountered this problem with SQL Server installation then by using an elevated administrator command prompt perform the following steps to re-register sqlctr100.dll, and run the file sqlctr.ini located in the binn directory for the instance:

  • Change the path to the BINN directory of the SQL Server instance you desire to correct.  (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn)
  • Execute unlodctr  <<REGISTERED SERVER NAME>>    For example: unlodctr MSSQL$SQLDev2008  for SQL Server with name instance (SQLDev2008) or unlodctr SQLAgent$SQLDev2008 for SQL Server Agent, …
  • Execute lodctr :<<perf-sql* matching the counters you desire to load>>, For example: lodctr perf-MSSQL$SQLDev2008sqlctr.ini for SQL Server  and lodctr perf-SQLAgent$SQLDev2008sqlagtctr.ini for SQL Server Agent, …  The /T is important to load the SQL Server performance counter provider as a trusted provider.
  • Cycle the remote registry service  net stop “Remote Registry” and net start “Remote Registry”.
  • Force a WMI synchronization using   winmgmt /resyncperfctr “<<PID>>”  where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

Now you can see the performance counter for that instance in PerfMon.

About these ads

10 thoughts on “I see no SQL Server 2008 Counters in Perfmon?

  1. Good stuff, Basit. Ran into this issue with a SQL Server 2008 R2 instance, I’ll give this a shot and update with the results.

    Like this

  2. I ran into the problem today. At the last step however, there is no WinPriv.exe listed in task manager. Did the process name change in Windows Server 2008? Yes, I’ve enabled “show processes for all users”.

    Like this

      • I tried winmgmt /resyncperfctr using the PID of WmiPrvSE.exe thinking that might be the new executable but it did not work. I reran perfmon and the counters still weren’t there.

        My server had several WmiPrvSE.exe’s running. I tried using the PID of all of them but still no dice.

        Like this

      • Try the following:

        Rebuilding the counters:
        cd c:\windows\system32
        lodctr /R
        cd c:\windows\sysWOW64
        lodctr /R

        Resyncing the counters with Windows Management Instrumentation (WMI):
        WINMGMT.EXE /RESYNCPERF

        Stop and restart the Performance Logs and Alerts service.
        Stop and restart the Windows Management Instrumentation service.

        This will rebuild all performance counters including extensible and third-party counters.

        Like this

      • If it happens again I will. I was fortunate that this server is only used as a log shipping secondary for about 500 databases. No end users were affected by taking it down to do a repair. I’m still at a loss though as to why they suddenly disappeared a few days ago. The server has been up for months and it was my understanding that this problem is only supposed to occur if you shut down a server while a perfmon collection is running.

        Like this

  3. FYI there is a simpler way to get the counters back though it involves down time. Run a “repair” operation against the SQL instance.

    Like this

    • Well I know that you can rebuild counters by running Repair but as you said the drawback is it needs down time. I work in a 24/7 environment where our team only got 5 hours down time in a month and that’s why rebuilding the counter is best option.

      Like this

  4. Pingback: Performance Monitors with TSQL « Understanding SQL

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