SQL Server performance counters missing!!
Past few hours been crazy one, breaking my head on a ‘Missing Performance Counters’ and now since this is resolved, I want to now off-load every bit of this issue from my brain so thought blogging it :-)
Issue: A customer is having 24x7 PERFMON counter collection and suddenly they found that all SQL Server related perform counters are missing.
Cause: While working on this for few hours, we could determine that a known issue described in Microsoft KB # 812915. The issue is specific to an ‘ancient’ SQL built (SQL2K SP3), wherein if SQL Server Service is shutdown (re-started) in between a perform collection then, after re-start, SQL Server related PERFMON counters do not appear in Performance object list.
Quick one – Ensure to always close the Performance console or Stop collection before you restart SQL Server.
Long (and better) one – Consider applying SQL 2000 SP4
However the story goes beyond. While working on this issue, I hit across so many distinct issues due to which SQL Perfmon counter goes missing and how to fix/retrieve them. I have consolidated a list of those articles / blogs where these distinct issues have been documented
SQL Server 6.5 / 2000
SQL Server 2005 / 2008
Expect to find changes to this blog post, as I might come across more issue on ‘missing performance counter’, but as of today, so-far-so-good
For SQL Server 2008 R2 I found that you should use "LODCTR perf-MSSQL$<INSTANCENAME>sqlctr.ini" instead of sqlctr.ini