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

Clustering Sites

http://www.clusterhelp.com/

http://www.msmvps.com/clustering

http://www.nw-america.com

Error: 5180, Severity: 22, State: 1

Error: 5180, Severity: 22, State: 1
Could not open FCB for invalid file ID 0 in database '%s'. Table or database may be corrupted.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276043

TargetServersRole

Use this "TargetServersRole" available on SQL Server 2000 SP3 and above to grant users who requires to view the all the jobs listed on SQL Server for a normal user.

Use msdb
EXEC sp_addsrvrolemember 'Corporate\HelenS', 'TargetServersRole'

Logshipping vs Database Mirroring

Applies to : SQL Server 2005

With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server

Transactional Consistency: All committed and un-committed are transferred

Server Limitation: Can be applied to multiple stand-by servers

Failover: Manual Failover Duration: Can take more than 30 mins

Role Change: Role change is manual

Client Re-direction: Manual changes required

With Database Mirroring:
Data Transfer: Individual T-Log records are transferred using TCP endpoints

Transactional Consistency: Only committed transactions are transferred

Server Limitation: Can be applied to only one mirror server

Failover: AutomaticFailover

Duration: Failover is fast, sometimes <>

Role Change: Role change is fully automatic

Client Re-direction: Fully automatic as it uses .NET 2.0

BCP Error on SQLState = S1000, NativeError = 0

When trying on the below BCP Command
bcp master..syslogins out D:\MSSQL\Backup\PrimaryLogship\syslogins.dat -N -S SQLServer -U sa -P password

getting the error message as

SQLState = S1000, NativeError = 0
Error = [MS][ODBC SQL Server Driver] Unable to open BCP host data-file

Resolution:
The problem occured because of "Incorrect Folder Name"

Where to see Windows Error Message nos?

Server: Msg 3202, Level 16, State 1, Line 1
1) Status = 64 Write on 'c:\temp.dat' failed, status = 64
2) Status = 112 Write on 'c:\temp.dat' failed, status = 112

It can be found at:

D:\Documents and Settings\pz5qrt>net HELPMSG 112

There is not enough space on the disk.

A SQL Server cluster resource goes to a "failed" state when you try to bring the resource online in SQL Server 2000

When you try to bring a SQL Server cluster resource online for a virtual instance of Microsoft SQL Server 2000, you may notice the following behavior:

The SQL Server cluster resource goes to a "failed" state and does not come online.

You receive a combination of the following error messages on the computer that owns the SQL Server cluster resource.
Error message 1An event that is similar to the following is in the system event log:
Date: 08/05/2004 Time: 1:11:19 AM Source: ClusSvc Category: Failover Mgr Type: Error Event ID: 1069 User: N/A Computer: Description: Cluster resource 'SQL Server ()' in Resource Group '' failed. Error message 2An error message that is similar to the following is in the Cluster log file:
00000644.00000944::2003/11/30-18:11:30.360 SQL Server : [sqsrvres] Unable to read the 'VirtualServerName' property. Error: d.00000644.00000944::2003/11/30-18:11:30.360 SQL Server : [sqsrvres] OnlineThread: Error d bringing resource online.Error message 3Error messages that are similar to the following are in the SQL Server error log file:
2003-11-30 17:00:37.27 server Error: 17826, Severity: 18, State: 1 2003-11-30 17:00:37.27 server Could not set up Net-Library 'SSNETLIB'.. 2003-11-30 17:00:37.27 spid13 Starting up database 'SPB'. 2003-11-30 17:00:37.27 spid12 Starting up database 'BD_MTA'. 2003-11-30 17:00:37.27 spid14 Starting up database 'BD_SPF'. 2003-11-30 17:00:37.27 server Error: 17059, Severity: 18, State: 0 2003-11-30 17:00:37.27 server Operating system error -1073723998: .. 2003-11-30 17:00:37.27 server Unable to load any netlibs. 2003-11-30 17:00:37.27 server SQL Server could not spawn FRunCM thread.
CAUSE
The resource-specific registry keys that correspond to the SQL Server cluster resource that you are trying to bring online are missing. This problem also occurs if the values that correspond to the resource-specific registry keys are not correct.
RESOLUTION
Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.To resolve this problem, you must manually re-create the resource-specific registry keys that correspond to the SQL Server cluster resource. To do this, follow these steps:
1.
Click Start, click Run, type Regedit, and then click OK.
2.
In Registry Editor, locate and select the following registry key:HKEY_LOCAL_MACHINE\Cluster\Resources\\Parameters
3.
Create the following registry values in the Parameters registry key:For a default instance of SQL Server:
•
InstanceNameValue Name: InstanceNameValue Type: REG_SZ Value Data: MSSQLSERVER
•
VirtualServerNameValue Name: VirtualServerNameValue Type: REG_SZValue Data: For a named instance of SQL Server:
•
InstanceNameValue Name: InstanceNameValue Type: REG_SZ Value Data:
•
VirtualServerNameValue Name: VirtualServerNameValue Type: REG_SZValue Data:
4.
Quit Registry Editor.After you create the resource-specific registry keys, you can bring the SQL Server cluster resource online successfully.If you notice that a SQL Server Agent cluster resource cannot be brought online, you must create the same set of resource-specific keys that correspond to the SQL Server Agent cluster resource.

http://support.microsoft.com/?kbid=883732

SQL Server Proxy Error Messages

If the SQL proxy account not set up the below error message would appearMsg 50001, Level 1, State 50001xpsql.cpp: Error 997 from GetProxyAccount on line 472

When SQL Server startup account does not have the 'Local Admin' privilegesMsg 50001, Level 1, State 50001xpsql.cpp: Error 997 from GetProxyAccount on line 472

When the SQL Server Startup account does not have the"Replace Process level Token" Privileges on Local security policy ,
Msg 50001, Level 1, State 50001xpsql.cpp: Error 1314 from CreateProcessAsUser on line 504

When the 'SQL Sever proxy account does not exist or deleted from the Windows.Msg 50001, Level 1, State 50001xpsql.cpp: Error 1326 from LogonUserW on line 488

DMZ - DeMilitarized Zone

Computer slang used for a protected network that sits between the Internet and the corporate network.
Sometimes called a perimeter network.

A DMZ is a computer network that is accessible from two other computer networks that have no direct contact with each other. Often, one of these networks is the Internet and the other is a local, internal network. There is no direct connection between the Internet and the local network, but both of them can access an intermediate network, a demilitarized zone. DMZs are often used for special servers, such as web servers, which must be accessible from two separate networks.

SQL Server Proxy Account

George,
Who require proxy access ?
This will allow jobs scheduled by non-admin users (such as Bank staff) to properly execute DTS packages.
SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell and own SQL Server Agent jobs. The administrators can assign appropriate security permissions to the proxy account to control the ability of these jobs to access resources in the network.

Why they needed?
1. SQL Proxy accounts are required to enable general users to schedule and execute SQL jobs for DTS packages (ETL).
2. In SQL Server 2000, a domain user cannot run the xp_cmdshell extended stored procedure even when the user has permissions
3. To create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator.
4. SQL Server Agent jobs also must execute in the security context of a Windows account. If the job is owned by a member of the sysadmin fixed server role, the job executes using the Windows account under which the SQL Server service is running. If the job owner is not in sysadmin, the job executes using the SQL Server Agent proxy account.
The following Microsoft knowledge base article explains what is required to execute a DTS package from an SQL Server job. This will allow jobs scheduled by non-admin users (such as Bank staff) to properly execute DTS packages.
http://support.microsoft.com/?kbid=269074

SQLServer Services not auto starting after reboot

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 12/01/2006
Time: 4:15:30 AM
User: N/A
Description:
The MSSQLSERVER service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7001
Date: 12/01/2006
Time: 4:15:30 AM

The SQLSERVERAGENT service depends on the MSSQLSERVER service which failed to start because of the following error: The service did not respond to the start or control request in a timely fashion.

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7009
Date: 12/01/2006
Time: 4:15:30 AM

Description:
Timeout (30000 milliseconds) waiting for the MSSQLSERVER service to connect.

What caused this error:Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 12/01/2006
Time: 4:15:05 AM
Description:
This computer was not able to set up a secure session with a domain controller in domain AU due to the following:
There are currently no logon servers available to service the logon request.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.

ADDITIONAL INFO
If this computer is a domain controller for the specified domain, it sets up the secure session to the primary domain controller emulator in the specified domain. Otherwise, this computer sets up the secure session to any domain controller in the specified domain.