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.
Basit's SQL Server Tips 
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.
LikeLike
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”.
LikeLike
Hi Chuck,
The Process in Windows Server 2008 is called WmiPrvSE.exe. I’ve tested this on process to rebuild counter on both Windows 2003 and Windows 2008.
Thanks,
Basit
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
FYI there is a simpler way to get the counters back though it involves down time. Run a “repair” operation against the SQL instance.
LikeLike
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.
LikeLike
Pingback: Performance Monitors with TSQL « Understanding SQL
>The /T is important to load the SQL Server….
What /T?
Where is it important?
LikeLike
Worked like a champ, thank you!!! The only change I had to make was in the resync command.
LikeLike