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
- FIX: "Performance monitor shared memory setup failed: -1" error message when you start SQL Server
- PRB: SQL Performance Monitor Counters Missing
- PRB: SQL performance counters may be missing after MDAC installation on a cluster
SQL Server 2005 / 2008
- How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components
- What should we do when the sql server performance counters are missing
- How to rebuild the performance counters of a SQL Server 2005 or 2008 instance
- How to manually rebuild Performance Counter Library values
- Missing Objects and Counters in Performance Monitor
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