SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.wordpress.com, opens in a new window]

Loading comments...