SQL Perf Counters return Zeros(0)
-
I have a server running WIN2k / SQL 2K Enterprise Edition SP3a applied and the SQL Perf counters return only Zero's... I have unloaded and reloaded the sqlctr.ini and rebooted the system still the same results..and I also attempted a resync of the counters also...
Jim Babington
Jbabington
Jbabington@hotmail.com -
This was removed by the editor as SPAM
-
I had a similar problem and didn't really ahve time to research it. And I had another option. I uninstalled, and reinstalled the server (detach, then attach dbs, script out logins).
Sorry
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
MSSQL Server 2000 Performance Counter issues
Draft
05-21-2004
Summary:
Noticed issues related to Performance counter displaying zeros on WIN 2000 Advanced Server MSSQL Server 2000 (SP3a MDAC 2.71.0940)When and If data for selected counters is consistently reported as zeroes the counters or the way you are using the performance tools, rather than just the absence of any nonzero data. Following are descriptions of the possible causes and solutions to problems that result in missing or zero counter values.
Example:
To view the issues open the Performance Monitor and select SQL Performance Counters All SQL related Performance Counters show a value = 0
Initial Solution:
The initial solution was to reload the SQL Performance Counters however this was not the core issue. After unloading and reloading the Counters with all applicable steps (Twice) I verified the system catalog MASTER.DBO.SYSPERFINFO Table and interestingly enough no data. Since the SYSPERFINFO table is referred as a secure system table running the sp_reconfigure with override (not actual syntax) you are not able to load the perf counters in this manner as these values are loaded through the NT Kernel and the SQLCTR80.DLL.
Common Cause:
It is common to lose the performance counters after a crash or server configuration issue. I have no instance that this has in fact occurred in this case of these servers. However the steps to reload the SQL Counters are in the Solution Section of this document.
- The process being monitored has stopped and, as a result, there is no data for the process in the performance tools. If you stopped the process manually, restart it to see the process in System Monitor. Otherwise, check Event Viewer for concurrent entries. You might find an error associated with this process.
- Verify all services and counter information present and the processes are running as expected
- Verify the Administrative rights to the Server you are attempting to monitor and if you don't have permission to access make the appropriate changes. This might occur if you are using a saved console that specifies a particular computer name. This causes System Monitor to report data as zeroes. It also causes System Monitor to start up slowly.
- Non-Issue if monitoring was tested under the administrative account
- The counter DLL was disabled after you selected the corresponding counters in a log or display. The performance tools will not detect that the counter was removed or disabled, but will report the counter data as zeroes.
- The SQL Server Services must be stopped
- Re-register the SQLCTR80.DLL (in the \\PATH\Program Files\Microsoft SQL Server\MSSQL\binn\ SQLCTR80.DLL Using REGSVR32.EXE SQLCTR80.DLL
Solution:
RELOADING SQL SERVER PERFORMANCE COUNTERS:
There are many reasons when the SQL Server counters will disappear, such as Permission, Server Crash, Registry Modification and Virus. To work around the symptom, please take the following actions.
Steps:
- Check if you have full permission to access the local folder: C:\Program Files\Microsoft SQL Server\MSSQL\ Make sure that you can access this folder or your current account is located in local administrator group.
- Make sure there is no third-party application which adds keys in Registry to disable the performance counters. You can open the registry to check if there is a key named "Disable Performance Counters" valued "1" in:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance] If it exists, please delete it.
- Under the registery key:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance] Make sure the following keys with the value DO EXIST. If not, please create a new one and add the value.
- `# Note # : The "Last Counter", "Last Help", "First Help", "WbemAdapFileSignature", "WbemAdapFileTime", and "First Counter" values can vary between SQL Server installations.
"Library"=" C:\PROGRA~1\MICROS~3\MSSQL$~1\BINN\SQLCTR80.DLL "
"Collect"="CollectSQLPerformanceData"
"Open"="OpenSQLPerformanceData"
"Close"="CloseSQLPerformanceData"
"Last Counter"=dword:000016ce
"Last Help"=dword:000016cf
"First Help"=dword:000015bb
"WbemAdapFileSignature"=hex:d1,70,dc,f8,a7,75,5e,e4,9e,e6,dd,91,9e,cd,06,65
"WbemAdapFileTime"=hex:d6,34,9d,95,64,0d,c2,01
"WbemAdapFileSize"=dword:00008238
"WbemAdapStatus"=dword:00000000dir
"First Counter"=dword:000015ba
- Unload the SQL Server Counters. In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)
- Reloading the SQL Server Counters
In command line, locate the current folder to C:\ProgramFiles\Microsoft SQL Server\MSSQL\BINN
- Execute "lodctr sqlctr.ini"
- Stop SQL Server Services and Restart the Services
i. In command line, execute "net stop mssqlserver"
ii. Execute "net start mssqlserver" (Or you can perform it in Service Manager or in System Services)
iii. Open PerfMon to check if the counters appear
1. In command line, execute "perfmon"
2. Add and check if the counter appear
Jbabington
Jbabington@hotmail.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply