Enabling Performance Counters in SQL Server 2000

Enabling Performance Counters in SQL Server 2000
This is an interesting problem we had in the Newsgroup. There are specific case where SQL Server's counters were missing. In this FAQ we assume that the installation of SQL Server 2000 is a default installation.

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:

1. 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.

2. Make sure there is no third-party application which add keys in Register 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.

3. Under the register 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:00000000
"First Counter"=dword:000015ba

4. Unload the SQL Server Counters. In command line, execute "unlodctr MSSQLServer" (Without quotation, the same as below)

5. Reloading the SQL Server Counters
(a) In command line, locate the current folder to C:\Program Files\Microsoft SQL Server\MSSQL\BINN
(b) Execute "lodctr sqlctr.ini"

6. Stop SQL Server Services and Restart the Services
(a) In command line, execute "net stop mssqlserver"
(b) Execute "net start mssqlserver"
(Or you can perform it in Service Manager or in System Services)

7. Open Perforam Monitor to check if the counters appear
(a) In command line, execute "perfmon"
(b) Add and check if the counter appear


PS: Note this FAQ is based on the conversation in the sqlserver.setup newsgroup

IO is frozen for snapshot / IO is thawed

Following errors registered on a SQL Server Errorlog:

19-12-2006 23:49 :58.27 Database USEDDB:IO is thawed
19-12-2006 23:49 :52.04 Database USERDB:IO is frozen for snapshot

It seems the problem would caused by

1. EMC frame tools to create an SRDF to copy to our disaster site.
This occurs on SAN while the snapshot backups take place.
While databases are 'frozen' they are essentially offline and no users will be
able to connect.

2. NTBackup. This program has a commandline switch called /SNAP.
Whenever a ntbackup, even to backup 2 simple text files, all open files are frozen for a moment. Somehow, if your machine contains a SQL Server instance, a dump for all databases is created to a virtual device.

Also refer:
Error 800423f4 appears in the backup log file when you back up a volume by using the Volume Shadow Copy service in Windows Server 2003

The Ntbackup.exe operation may break the differential backup chain of a SQL Server database and may invalidate the differential backups when you perform a snapshot backup of the database