Performance Metrics in SQL Server Stop Working When You Stop SQL Server

I was diagnosing performance problems for a customer when several performance-related counters suddenly stopped working. The affected information included all SQL Server Performance Monitor counters, waittype information from sysprocesses, and all I/O-related activity from the fn_virtualfilestats() function. I was confused until I ran across the Microsoft article "FIX: 'Performance monitor shared memory setup failed: -1' error message when you start SQL Server" at;en-us;812915. The article discusses a bug in SQL Server 2000 Service Pack 3 (SP3) that causes you to lose Performance Monitor counters if System Monitor is running from the performance console when you stop SQL Server. The article also tells how you can obtain the supported hotfix. Unfortunately, the article doesn't offer a good workaround, so I've had to stop and restart SQL Server when this problem happens to re-enable the Performance Monitor counters and other performance-related metrics. You'll know you're having this problem if the SQL Server error log reports "Performance monitor shared memory setup failed: -1." The Microsoft article doesn't explicitly mention that fn_virtualfilestats() stops working or that other sources of performance information might also be affected. Keep these things in mind if your monitoring tools suddenly go haywire.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.