Chapter 10 - Troubleshooting
This chapter provides information about troubleshooting and resolving the most common errors you may encounter while using Microsoft SQL Server. If you encounter a problem while using SQL Server, check the SQL Server error log for entries that might help identify the cause. For more information, see SQL Server Books Online.
Isolating Connection Problems
When a DB-Library application has problems connecting to Microsoft SQL Server, there might be a problem with SQL Server, the network, or both. Regardless of which network you are running on, there are several items that you can check to isolate the problem. Check that:
For servers running Microsoft Windows NT, the local connection to SQL Server over Named Pipes is available. You can test a local Named Pipes connection by using osql with no servername argument. If you cannot make a local connection using Named Pipes, either you are using an invalid login or there is a problem with SQL Server.
For servers running Microsoft Windows 95 or Windows 98 operating systems, the local connection to SQL Server uses the Shared Memory Net-Library.
The network components match the requirements specified in SQL Server Books Online.
The default client Net-Library is appropriate for your network. You can determine and, if necessary, change the default client Net-Library by using SQL Server Client Network Utility in the Microsoft SQL Server program group.
The network connection information on the server is appropriate for your network (if you are running SQL Server and listening on other interprocess communication (IPC) mechanisms in addition to Named Pipes). You can determine and, if necessary, change which networks SQL Server is listening on using SQL Server Network Utility** **in the Microsoft SQL Server program group.
The network connection between the client workstation and the server is established. (The procedures for testing network connections for all supported networks are provided in the following sections.) If you test the network connection and determine that it is not open, check that:
The hardware connection is not disabled due to loose connectors or plugs.
The network software is installed and running on both the client workstation and the server.
Orphaned Sessions
A client may have its connection abruptly severed from the server, and as a result, the client process may be unable to tell the network to close the connection properly. This may occur for many reasons, including power failures on the client. Microsoft SQL Server does not proactively probe the status of a client connection. Instead, it relies on Microsoft Windows NT to notify it when a connection needs to be terminated or closed. Windows NT monitors connections and continues to report them as active to SQL Server for the duration of the KeepAliveTime for TCP/IP or SessionKeepAlive for NetBios, which affects Named Pipes clients. SQL Server continues to keep locks owned by the client active until they are killed, or until the connection is terminated or closed by Windows NT.
The following questions and answers provide frequently requested information on orphaned sessions.
Q: What is an orphaned session?
A: An orphaned session is a session that remains open on the server side after the client has disconnected.
Do not confuse orphaned sessions with orphaned users. Orphaned users are created when a database is backed up and restored to another system that does not have a corresponding user account configured. For more information about orphaned users, see SQL Server Books Online.
Q: When do orphaned sessions occur?
A: Orphaned sessions occur when the client is unable to free network connections it is holding when it terminates.
If the client terminates cleanly, Windows NT closes the connection and notifies SQL Server. If SQL Server is processing a client command, it will detect the closed connection when it ends the session. Client applications that crash or have their processes killed (for example, from Task Manager), are cleaned up immediately by Windows NT, rarely resulting in an orphaned session.Orphaned sessions often are caused by a client computer losing power unexpectedly or being shut down improperly. Orphaned sessions can also occur due to a "hung" application that never completely terminates, resulting in a dead connection. Windows NT will not know that the connection is dead and will continue to report the action as active to SQL Server. SQL Server, in turn, keeps the session open and continues to wait for a command from the client.
Q: What problems can orphaned sessions cause?
A: Open sessions take up one of the SQL Server network connections. The maximum number of connections is limited by the number of server CALs; therefore, orphaned sessions may prevent other clients from connecting.
Typically, a more important issue is that open sessions use server resources, and may have open cursors, temporary tables, or locks. These locks can block other connections from performing useful work, and can sometimes be the result of a major backlog of locks. In severe cases, it can appear that SQL Server has stopped working.
Q: How can I tell if an orphaned session exists and what problems it might be causing?
A: sysprocesses (or stored procedures, such as sp_who) reports information on existing server sessions. Possible orphaned sessions can be identified if the status of a process is AWAITING COMMAND and the interval of time, found by subtracting last_batch from GETDATE(), is longer than usual for the process. If the session hostname is known to be down, it is orphaned.
Q: How do I resolve orphaned sessions?
Windows NT checks inactive sessions periodically to ensure they are active. If a session does not respond, it is closed and SQL Server is notified. The frequency of the checking depends on the network protocol and registry settings. However, by default, Windows NT performs a check only every one or two hours, depending on the protocol used. These configuration settings can be changed in the registry.To close an orphaned SQL Server session, use the KILL command. All resources held by the session are then released.If orphaned sessions become a problem, registry settings can be changed on Windows NT to increase the frequency at which clients are checked to verify they are active.Warning Do not change these settings on computers running Microsoft Windows 95 or Windows 98 operating systems.Consider the effect changing these settings may have on other applications on your system, in particular, applications with Internet connectivity, such as Microsoft Internet Information Services (IIS) or Microsoft Internet Explorer. In addition, consider the effects of using connections that are charged on a per-packet basis.Caution Editing the registry is not recommended; inappropriate or incorrect changes can cause serious configuration problems for your system. Only experienced users should use the Registry Editor. For more information, see your Windows NT documentation.The registry entries can be altered from HKEY_LOCAL_MACHINE by double-clicking SYSTEM, expanding CurrentControlSet, and then clicking Services.KeepAliveTime for TCP/IPKey: Tcpip\ParametersValue Type: REG_DWORD - Time in millisecondsValid Range: 1 - 0xFFFFFFFFDefault: 7,200,000 (two hours)Description: The parameter controls how often TCP attempts to verify that an idle connection is still intact by sending a keep alive packet. If the remote system is still reachable and functioning, it will acknowledge the keep alive transmission. Keep alive packets are not sent by default; this feature may be enabled on a connection by an application.SessionKeepAlive for Named PipesKey: Netbt\ParametersValue Type: REG_DWORD - Time in millisecondsValid Range: 60,000 - 0xFFFFFFFFDefault: 3,600,000 (1 hour)Description: This value determines the time interval between keep alive transmissions on a session. Setting the value to 0xFFFFFFF disables keep alives.
Note Do not increase the ping frequency to less than 1 minute, as network I/O and CPU usage for pings may become excessive.
Named Pipes Client Connections
The following procedure describes how to test a network connection when using Named Pipes as the IPC mechanism.
To test a Named Pipes connection
At the operating system command prompt on the client workstation, type:
net view \\servername
When using net view, servername is the name of the server to which you want to connect.
For example, to check the connection between a Named Pipes client and a server named \\SEATTLE1, type the following on the client:
net view \\SEATTLE1
If the connection is open, the output looks something like this:
Shared resources at \\SEATTLE1 SQL Server Sharename Type Used as Comment ---------------------------------------------------- PUBLIC Disk Public Files The command completed successfully.
To verify connection to a server's named pipe
From a command prompt, type:
net use \\ servername \IPC$
When using net use, servername is the server to which you want to connect.
For example:
net use \\SEATTLE1\IPC$ The command completed successfully
If the connection between the client workstation and the server is open but you still cannot connect to Microsoft SQL Server, test the network and local Named Pipes using the makepipe and readpipe utilities.
Two utilities included with SQL Server are designed to help test the integrity of network Named Pipes. The makepipe and readpipe utilities are installed during installation of both the client and server components. There are different versions of these utilities for the different operating systems on which they run: makepipe runs on Microsoft Windows NT; readpipe runs on Windows NT, Microsoft Windows, and MS-DOS. Be sure to use the correct version for the operating system that you are testing. (The version that runs on Windows is named readpipe. If the SQL Server tools are installed, readpipe is located in the \Msqql7\Binn directory; no icon is created for it.)
To test the integrity of the network Named Pipe services
At the operating system command prompt on the server, type:
makepipe
The makepipe utility returns the following information:
Making PIPE:\pipe\abc
read to write delay (seconds):0 Waiting for Client to Connect...
SQL Server is waiting for a client to connect.
At the operating system command prompt on the client workstation, type:
readpipe /S server_name /D string
When using readpipe, server_name is the network server name of the SQL Server on which makepipe was started and string is a test character string. If the string contains spaces, it must be enclosed in double quotation marks. There are no spaces between /S and the server name, and no spaces between /D and the string.
For example, to connect to a SQL Server installation named MYSERVER, type one of the following:
readpipe /Smyserver /Dhello
readpipe /Smyserver /D"hello there" readpipe /Smyserver /D'hello there'
The strings specified in the first two **readpipe** statements are treated identically.
If a network Named Pipes connection can be established, the client workstation returns the following information to each of the commands above, respectively:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">SvrName:\\myserver
PIPE :\myserver\pipe\abc DATA :hello Data Sent: 1 : hello Data Read: 1 : hello
SvrName:\myserver PIPE :\myserver\pipe\abc DATA :hello there Data Sent: 1 :hello there Data Read: 1 :hello there
SvrName:\myserver PIPE :\myserver\pipe\abc DATA :hello Data Sent: 1 : 'hello Data Read: 1 : 'hello
If a network named pipe connection can be established, the **makepipe** utility returns information similar to this:
<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Waiting for Client to Connect...
Waiting for client to send... 1
Data Read: hello
Waiting for client to send... 2
Pipe closed
Waiting for Client to Connect... Waiting for client to send... 1 Data Read: hello there
Waiting for client to send... 2
Pipe closed
Waiting for Client to Connect... Waiting for client to send... 1
Data Read: 'hello
Waiting for client to send... 2 Pipe closed
Waiting for Client to Connect...
At this point, SQL Server is waiting for a client to connect. The **readpipe** utility can be run from other workstations.
- When testing is complete, go to the server where the makepipe utility is running and press either CTRL+BREAK or CTRL+C.
If the results are different from those in Step 2, network named pipe services are not available. If you are using Named Pipes as the IPC mechanism, clients cannot connect to SQL Server until a Named Pipe is available. These utilities attempt to open and use a named pipe; they do not stress the named pipe connection.
TCP/IP Sockets Client Connections
The following procedure describes how to test a network connection when using TCP/IP Sockets as the IPC mechanism.
To test a TCP/IP Sockets connection
At the operating system command prompt on the client workstation, type:
ping {ip_address | server_name}
When using ping, ip_address is the TCP/IP address of the server to which you want to connect and server_name is the name of the server to which you want to connect.
For example, to check the connection between a TCP/IP Sockets client workstation and a server at the TCP/IP address 11.1.4.70, type the following on the client workstation:
ping 11.1.4.70
If the connection is open, the output looks something like this:
[1] echo received from 11.1.4.70 with roundtrip < 50 sec
This example checks the connection between a TCP/IP Sockets client and a server named SEATTLE1:
ping SEATTLE1
If the connection is open, the output looks something like this:
[1] echo received from SEATTLE1 with roundtrip < 50 sec
Troubleshooting SQL Server Setup
SQL Server Setup is designed to be as problem free as possible; however, there may be situations that will interfere with the installation of Microsoft SQL Server. The most common errors are simple to diagnose and resolve. The resolution may involve freeing up disk space, shutting down other applications, or restarting the computer to unlock shared files.
For more information, see SQL Server Books Online.
If the installation fails, the Setup program might remove all installed components.
Before running the Setup program or installing additional SQL Server components, be sure to:
Shut down all services.
Use Services in Control Panel to shut down the MSSQLServer and SQLServerAgent services. Shut down the MSSearch service if you installed Full-text Search. Shut down the MSDTC service if you installed MS DTC.
Check that the SQL Server Service Manager icon in the taskbar is closed.
Any attempt to run the Setup program with the SQL Server Service Manager icon present (or any services still running) may cause the setup process to fail.
Remove the read-only attribute for all ODBC* files. On computers running Microsoft Windows NT these files are located in the \System32 directory. On computers running Microsoft Windows 95 or Windows 98 operating systems, these files are located in the system directory.
If you cannot remove the read-only attribute on the ODBC* files, SQL Server produces a dialog box that allows you to retry the updating of the ODBC* files.
Understand that servers running Windows NT require Named Pipes. Cleaning named pipes during SQL Server installation does not prevent installation of named pipes. Because servers running Windows NT require named pipes, there is no way to uninstall Named Pipes on a server running Windows NT.
Setup Troubleshooting: Checklist
Read the error message. SQL Server Setup translates most error codes received from the operating system.
With the error dialog box showing, open Sqlstp.log in the \Windows or \WinNT directory. Check the last few events in the log to see if any problems occurred before the error message was generated.
If this is a custom installation and the component that failed to install properly is the Full-text Search (MSSearch) service, check the Mssearch.log in the \Temp directory to see if any problems occurred.
Continue past the error message dialog box. Some error messages are just warnings. The Setup program may still finish successfully.
If the Setup program fails, and you cannot diagnose and fix the problem yourself, make a copy of Sqlstp.log and Setup.iss from the \Windows or \WinNT directory and, if you installed Full-text Search, make a copy of the Mssearch.log from the \Temp directory.
Note The Setup program may encounter problems installing MS DTC on computers with multiple network cards or SPX installed. If the Setup program stops responding, check the Sqlstp.log in the \Windows or \WinNT directory to see if MS DTC is being installed. If this is the problem, uninstall one of the network cards or SPX, and then retry the Setup program.
Testing an Installation of SQL Server 7.0
Before a server installation is complete, SQL Server Setup starts and connects to the server multiple times. When the installation is complete, you can test the installation yourself and connect to it locally by running the osql utility from the server.
SQL Server Setup Frequently Asked Questions
Here are some of the frequently asked questions regarding SQL Server Setup.
Q: What do I need to do if I have an unsuccessful installation or I find an error in the Setup program?
A: If your installation fails and you are unable to determine the cause, or you find an error in the Setup program, find and save:
The Sqlstp.log file, located in your \Windows or \WinNT directory.
The Setup.log files, located in your \Windows or \WinNT directory.
Any other files left in the target program directory (by default, \Mssql7).
The Cnfgsvr.out file located in the \Install directory of the target program directory (by default, \Mssql7\Install).
Any error logs located in the \Mssql7\Log directory.
If the Setup program fails, changes to the operating system are rolled back, including removal of any copied files and removal of changes to the registry.
Q: Why aren't some directories deleted on uninstall? Why does the final uninstall report always report that it cannot remove folders?
A: The UnInstallshield installer removes only what it installs.
Files created after installation is complete, such as tempdb, are not part of the installation record and will be unknown to UnInstallshield. Therefore, UnInstallshield is not able to remove these files or their associated directories. Sqlsun.dll, which runs after UnInstallshield is complete, removes files such as tempdb and their associated folders. Sqlsun.dll will not remove error logs, trace files, user data, or their associated folders if they are generated in the original directory structure.
Informational Files Created by SQL Server Setup
These informational files are generated to locate any problems during setup:
The Sqlstp.log file, located in your \Windows or \WinNT directory. For example, C:\WinNT\Sqlstp.log.
The most recent error log, located in the \Log directory of the target installation directory (by default, \Mssql7). For example, C:\Mssql7\Log\Errorlog.
The Cnfgsvr.out file, located in the Install directory of the target installation directory (by default, Mssql7). For example, C:\Mssql7\Install\Cnfgsvr.out.
Troubleshooting the SQL Server Upgrade Wizard
The SQL Server Upgrade Wizard is designed to be as problem free as possible; however, there are situations that may interfere with upgrading Microsoft SQL Server version 6.x databases to SQL Server 7.0. The most common upgrade error is the failure to create an object in SQL Server 7.0. In many cases, the problem is simple, such as running out of disk space. In any case, the SQL Server Upgrade Wizard creates detailed logs specifying the problem.
Completing the SQL Server Upgrade Wizard
The SQL Server Upgrade Wizard performs a version upgrade using the options you specified. The Microsoft SQL Server version 6.x server and data are left intact throughout the version upgrade process.
During the SQL Server Upgrade Wizard process:
User stored procedures are verified against the contents of syscomments for inconsistencies.
All logins, users, and permissions are validated.
If the SQL Server Upgrade Wizard detects any problems, a dialog box is displayed with this text:
One or more warnings have been logged. Please read the next screen carefully before you begin your upgrade.
The Summary of Warnings dialog box displays inconsistencies found in the user objects of accounts. Users should not continue until these are resolved. This output file is located in the \Mssql\Upgrade\<SQLServer_date_time> directory. The file name is associated with the database name and ID, "check65-<dbid><dbname>_err.out" (for example, "check65-007mypubs_err.out"). If the user continues without fixing the listed errors, check the relevant files for objects, logins, and invalid permissions.
If stored procedures have been renamed using sp_rename, the source stored in syscomments must be changed. Drop and re-create the procedure using the new name in the CREATE PROCEDURE syntax.
Upgrade Log Files
Each time you run the SQL Server Upgrade Wizard, a subdirectory is created in the C:\Mssql7\Upgrade directory. The subdirectory name consists of the server name and the current date and time to distinguish multiple runs of the SQL Server Upgrade Wizard (for example, SQLCONV1_092198_151900).
Inside this subdirectory are a number of descriptively named log files describing each of the upgrade steps. Also inside is another subdirectory for each upgraded database, including the master database. Inside each database folder are log files indicating the success of the creation of different types of objects in that database. Files that end in .ok indicate that all instances of that type of object were created successfully. Files that end in .err indicate that at least one instance of that type of object was not created successfully. The error files list each failed object creation statement and the reason the object was not created successfully.
Any log files that indicate a problem are listed at the end of the SQL Server Upgrade Wizard for easy access.
Programming Troubleshooting
Problems reported by the Microsoft SQL Server ODBC driver are not all related to ODBC errors. The driver is the interface between an ODBC application and all of the SQL Server components. All SQL Server errors are returned through the SQL Server ODBC driver. Diagnosing errors reported by the driver includes:
Diagnosing ODBC connection errors.
Diagnosing general ODBC errors.
Troubleshooting the SQL Server ODBC Driver
Troubleshooting the Microsoft SQL Server ODBC driver should include a review of the following issues:
If applications prepare a lot of SQL, it is recommended that these clients release the SQL as soon as possible to free memory. For more information, see SQL Server Books Online.
Few or no servers appear in the Register Server Wizard in SQL Server Enterprise Manager after either clicking Next in the Welcome dialog box or executing osql-L.
Microsoft Windows NT enumerates servers, but Microsoft Windows 95 and Windows 98 do not. On Windows 95 or Windows 98 operating systems, the only servers enumerated either by using the Register Server Wizard or by executing osql-L are those servers configured explicitly using the Client Network Utility.
Windows NT enumerates servers by network broadcast, listening for a fixed time period. Only those servers added explicitly or those receiving the broadcast and replying within the specified time limit are enumerated.
Therefore, on a Windows NT server, it is possible to see a large list of enumerated servers at one point in time, and then see a smaller list of enumerated servers just a few seconds later. This absence of enumerated servers can be caused by one or more temporarily busy or unavailable network computers.
On the Windows 95 or Windows 98 operating systems, a connection cannot be established to any ODBC application if the Shared Memory Net-Library file named dbmsshrn.dll was either renamed or deleted. This Shared Memory Net-Library file is required for local connections.
All driver-specific connection attributes are reset when an application uses connection pooling. After a connection is complete, an application using connection pooling must set all driver-specific connection attributes.
To eliminate the row count messages from DML statements inside a procedure, use SET NOCOUNT ON as the first statement in the procedure.
Character and binary parameters are padded to the size specified in SQLBindParameter. To disable, clear the AnsiNPW check box.
The code page of the server must be installed on the client workstation unless automatic translation is disabled.
Parameters must be specified as SQL_PARAM_INPUT unless the parameters are used with a stored procedure and declared as OUTPUT parameters.
When a 2.65 or earlier version of the SQL Server ODBC driver is used and connected to a server running SQL Server 7.0, the server classifies the application as a pre-SQL Server 7.0 client and does not support certain features introduced in SQL Server 7.0. For more information, see SQL Server Books Online.
For more information about the changes from the version 2.0 ODBC driver to the version 3.0 ODBC driver, see the Microsoft ODBC 3.0 Programmer's Reference and SDK Guide.
Diagnosing ODBC Connection Errors
This topic discusses how to diagnose issues that may arise when connecting to Microsoft SQL Server using the SQL Server ODBC driver.
A connection attempt using an ODBC data source fails and a call to SLQDiagRec () returns:
szSqlState = "IM002", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
The ODBC driver manager could not find the ODBC data source. Make sure the data source name was given correctly. Also make sure the data source name was defined using the same Microsoft Windows account the application is running under, or is a system data source. If the application is running as a Microsoft Windows NT service, the data source must be a system data source.
For more information, see SQL Server Books Online.
A connection attempt fails and a call to SQL Error () returns:
szSqlState = "IM003", *pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC Driver Manager] specified driver could not be loaded"
The driver manager could not load the driver DLL, Sqlsrv32.dll, successfully. Make sure a valid version of Sqlsrv32.dll is in the client's path.
A connection attempt fails and a call to SQLError () returns:
szSqlState = "S1000", *pfNativeError = 126,
szErrorMsg="[Microsoft][ODBC Driver Manager] Unable to load communication module. Driver has not been correctly installed."
The SQL Server driver could not load the SQL Server client Net-Library. Verify that the ODBC data source specifies a valid Net-Library name. Verify that a valid version of the Net-Library .dll is in the client's path. This may also occur if the .dlls and files making up the underlying network protocol stack, such as Novell's SPX/IPX, or a TCP/IP protocol stack, are not installed properly. Verify the components with the network administrator, or reinstall the client network components.
For other network connectivity errors, SQLDiagRec() will return errors in which the szErrorMsg string has this format:
[Microsoft][ODBC SQL Server Driver][Net-Libraryname]
Net-Libraryname is the display name of a SQL Server client Net-Library (for example, Named Pipes, Shared Memory, Multiprotocol, TCP/IP Sockets, NwLink IPX/SPX, or Banyan VINES). The remainder of the error messages contains the Net-Library function called and the function called in the underlying network API by the TDS function. The pfNative error code returned with these errors is the error code from the underlying network protocol stack. Work with the network administrator or your SQL Server support vendor to determine a likely cause for the network error.
Diagnosing General ODBC Errors
This topic discusses how to diagnose issues that may arise when using the Microsoft SQL Server ODBC Driver.
The szErrorMsg string returned by SQLDiagRec() starts with:
"[Microsoft][ODBC SQL Server driver][DBMSxxxx]"
If DBMSxxxx is the name of a SQL Server client Net-Library, the problem is a connectivity or network problem. For more information, see SQL Server Books Online.
The szErrorMsg string returned by SQLDiagRec() starts with:
"[Microsoft][ODBC SQL Server driver][SQL Server]"
The error is coming from SQL Server. The pfNative variable returned by SQLError() is the SQL Server error code. Follow the directions for this error number in ODBC Error Message Format. Also, you should review the problem with the database administrator.
If you are experiencing difficulties with stored procedures that reference temporary tables on SQL Server 6.0 or later, make sure that the CREATE TABLE statements for the temporary tables specify NULL or NOT NULL for each column. For more information, see SQL Server Books Online.
If you are experiencing differences in behavior between DB-Library and ODBC clients, or in ODBC clients upgraded from earlier versions of SQL Server, these could be due to different options set by the SQL Server ODBC driver. For more information, see SQL Server Books Online.
If you are experiencing Microsoft Access conformance errors, or if calls to the ODBC catalog API functions are failing, check that the right version of Instcat.sql was run in the target server. For more information, see SQL Server Books Online.
If you are experiencing syntax errors from either SQL Server:
szSqlState = "37000", *pfNativeError = 170,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server] Line 1: Incorrect syntax near '*'."
or the ODBC SQL Server Driver, you need to determine if the problem is with the SQL statements given to the SQL Server ODBC driver by the application, or with the Transact-SQL statements generated by the driver.
Use the ODBC trace to see the SQL statements passed from the application to the SQL Server ODBC driver. The ODBC trace is activated from the **ODBC** icon in Control Panel.
If the trace shows that the SQL statements coming from the application are not using valid Transact-SQL or ODBC SQL syntax, diagnose the application.
If the trace shows that the application is passing valid Transact-SQL or ODBC SQL statements to the driver, use SQL Server Profiler to trace the Transact-SQL statements sent from the SQL Server ODBC driver to SQL Server. For more information, see SQL Server Books Online.
Troubleshooting SQL-DMO
After a successful installation of the Microsoft SQL Server version 7.0 client utilities, SQL-DMO files can be found in these locations with these names:
- In the %SystemRoot%\system32 directory, Sqlwoa.dll.
In addition to the above files, a successful installation of SQL-DMO includes:
ODBC version 3.5
SQL Server ODBC driver version 3.70
Odbcbcp.dll
If the installation of client utilities fails, it is possible that not all SQL-DMO files will be present or some may not be registered properly.
To register the SQL-DMO components
From \Mssql7\Binn\Resources\<language> directory, execute:
\MSSQL7\BINN\REGSVR32 SQLDMO.RLL
From any directory, execute:
\MSSQL7\BINN\REGSVR32.EXE \MSSQL7\BINN\resources\1033\SQLDMO.RLL
SQL Namespace
To use SQL Namespace (SQL-NS), you must have these files in these locations with these names and the SQL-DMO files mentioned above:
- In the %SystemRoot%\system32 directory, Sqlwoa.dll.
If the installation of client utilities fails, it is possible that not all SQL-NS files will be present or some may not be registered properly.
To register the SQL-NS components
From \Mssql7\Binn\Resources\<language> directory, execute:
\MSSQL7\BINN\REGSVR32 SQLNS.RLL
From any directory, execute:
\MSSQL7\BINN\REGSVR32.EXE \MSSQL7\BINN\resources\1033\SQLNS.RLL
Server and Database Troubleshooting
Servers running Microsoft SQL Server databases can have errors specific to the following areas:
Databases marked as suspect
Alerts
Locks
Jobs
Microsoft Windows NT services related to SQL Server
Interaction with the operating system
SQL Server Agent
Resetting the Suspect Status
Microsoft SQL Server returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:
Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.
sp_resetstatus, shown below, turns off the suspect flag on a database but leaves all other database options intact.
Caution Use sp_resetstatus only when directed by your primary support provider. Otherwise, you might damage your database. For more information, see SQL Server Books Online.
Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:
USE master GO sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO
After the procedure is created, immediately disable updates to the system tables:
sp_configure 'allow updates', 0 GO RECONFIGURE WITH OVERRIDE GO
sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure. The syntax is:
sp_resetstatus database_name
This example turns off the suspect flag on the PRODUCTION database.
sp_resetstatus PRODUCTION
Here is the result set:
Database 'PRODUCTION' status reset! WARNING: You must reboot SQL Server prior to accessing this database!
sp_resetstatus Stored Procedure Code
Here is the code of the sp_resetstatus stored procedure:
CREATE PROC sp_resetstatus @dbname varchar(30) AS DECLARE @msg varchar(80) IF @@trancount > 0 BEGIN PRINT "Can't run sp_resetstatus from within a transaction." RETURN (1) END IF suser_id() != 1 BEGIN SELECT @msg = "You must be the System Administrator (SA)" SELECT @msg = @msg + " to execute this procedure." RETURN (1) END IF (SELECT COUNT(*) FROM master..sysdatabases WHERE name = @dbname) != 1 BEGIN SELECT @msg = "Database '" + @dbname + "' does not exist!" PRINT @msg RETURN (1) END IF (SELECT COUNT(*) FROM master..sysdatabases WHERE name = @dbname AND status & 256 = 256) != 1 BEGIN PRINT "sp_resetstatus can only be run on suspect databases." RETURN (1) END BEGIN TRAN UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = @dbname IF @@error != 0 OR @@rowcount != 1 ROLLBACK TRAN ELSE BEGIN COMMIT TRAN SELECT @msg = "Database '" + @dbname + "' status reset!" PRINT @msg PRINT " " PRINT "WARNING: You must reboot SQL Server prior to " PRINT " accessing this database!" PRINT " " END GO
Troubleshooting Alerts
If you are experiencing problems with alerts, read the solutions detailed here.
An alert is not firing.
Verify that the SQLServerAgent and EventLog services are running.
Verify that the event appears in the Microsoft Windows NT application log.
Launch the Windows NT Event Viewer. If the event is not in the log, check the log settings. On the Log menu, click Log Settings, and then in the Change Settings for Log box, select Application. If needed, set these options to the specified values.
Setting
Value
Maximum Log Size
Minimum of 2,048 KB (2 MB)
Event Log Wrapping
Overwrite Events as Needed
Note Also check the Microsoft SQL Server error log; events written to the Windows NT application log are also written to the SQL Server error log. To focus the search on the cause of the problem, compare the dates and times for events between the SQL Server error log, the SQL Server Agent error log, and the Windows NT application log.
Verify that the alert is enabled.
Verify that the history values of the alert (for example, the occurrence count and last occurred values) are changing.
Verify that the counter value is at, above, or below the defined threshold value for a minimum of 20 seconds.
SQL Server Agent polls the performance counters at 20-second intervals.
Important Using a frequency higher than 20 seconds increases the processing overhead for SQL Server.
If a counter spikes for only a few seconds, which satisfies the performance condition, there is a high likelihood that SQL Server Agent will fail to see the spike; the alert will not fire.
An alert is firing, but the responsible operator is not receiving notification.
Check the operator and notification information to verify you have entered the correct e-mail, pager, and net send addresses.
Test the e-mail, pager, and net send addresses.
Check the operator's on-duty schedule.
Check the SQL Server Agent error log for any e-mail problems.
An alert is firing, but the notification is not timely.
The probable causes for this include:
The Delay between responses setting for the alert is too high.
The alert response is complex, requiring many operator notifications.
Note Send notifications to as few operators as possible. For example, send notifications to one group e-mail address rather than notifying several individual operators.
This error appears in the SQLServerAgent error log on Windows 95 or Windows 98 servers: "The common event system is being restarted after function ProduceEventsFromSS returned error 44, 'Unable To Connect'"
This may indicate incorrect registered server information. Verify that the registered server information for the local server is correct and that the registered login name is a member of the sysadmin fixed database role.
The Windows NT application log fills rapidly with the same error.
The CPU usage is high.
The number of alert responses is high.
Because SQL Server Agent both depends on and monitors SQL Server, SQL Server Agent can become caught in an endless loop of firing the same alert. This generally occurs when SQL Server runs out of an essential global resource and an alert has been defined on this event.
When the number of alerts raised exceeds the SQL Server Agent alert processing rate, a backlog is created.
To eliminate an alert processing backlog
Increase the amount of time in the Delay between responses setting.
Correct the global resource problem to prevent recurring alerts from using all your resources.
Configure an error to be nonalert-generating.
Important Configuring an error to not generate an alert can be performed only within the registry. This solution should be used only as a last resort.
Clear the Windows NT application log if: the backlog is not clearing, you do not want to wait for SQL Server Agent to clear the backlog, or you want an empty, unpopulated Windows NT application log.
Caution Clearing the Windows NT application log using the Clear All Events option on the Log menu deletes all events from the error log, including those unrelated to SQL Server.
To configure an error to not generate an alert
Start the Registry Editor.
Locate the following registry key:
HKEY_LOCAL_MACHINE
\SOFTWARE \Microsoft \MSSQLServer \SQLServerAgent \NonAlertableErrors
Type the error number.
The list of nonalertable errors can be a maximum of 1,024 characters, should not contain spaces, and items must be separated by commas (,). Any error number in the list that appears after the number 0 will generate an alert. In this way, the entire list can be suspended temporarily if a 0 is the first list item. For example, assume that the list consists of
1204,0,100
In this example, only error number 1204 does not generate an alert. Because error number 100 follows error number 0 in the list, it will generate an alert.
Important Never remove the default nonalert-generating error, error 1204. Error 1204 defines those conditions known to lead to recursive alert generation. Removing this error will hamper attempts to resolve recursive alert generation.
Troubleshooting Data Transformation Services
Following is information about errors that might be generated while using DTS Designer:
When using DTS on Alpha platforms, the Microsoft OLE DB Provider for Jet, supplied by default with Microsoft SQL Server, works when configuring connections to Microsoft Access. However, the ISAM components needed to make connections between that provider and data sources such as Microsoft Excel, Paradox, and dBase files are not installed. If you try to configure any of these data sources on an Alpha computer, you will receive an error message.
If a connection is created for a database user who does not have permission to access the model database, when opening the transformation properties, the user will receive the message "Unspecified error. Server user '<user name>' is not a valid user in database 'model'". This message occurs because the user cannot see the provider type information. However, the error is informational and does not prevent the user from creating the transformations.
If you receive the message "Cannot find the specified file" when attempting to access a DTS package saved to a COM-structured storage file, verify that:
The directory path is specified correctly.
Permissions are set correctly.
Note It is possible that the file is corrupt and that the stream cannot be detected.
Troubleshooting Locking
Two locking problems that an application may encounter are deadlocking and blocking.
Deadlocking
Deadlocking is a condition that can occur on any system with multiple users, not only on a relational database management system (RDBMS). A deadlock occurs when two users (or sessions) have locks on separate objects and each user wants a lock on the other's object. Each user waits for the other to release their lock. Microsoft SQL Server detects when two connections have gotten into a deadlock. One of the connections is chosen as a deadlock victim. The connection's transaction is rolled back and the application receives an error.
If deadlocks become a common occurrence to the point that their rollbacks are causing excessive performance degradation, you may need to perform more in-depth investigation. Use trace flag 1204. For example, this command starts SQL Server from the command prompt and enables trace flag 1204.
c:\mssql7\binn\sqlservr -T1204 /dc:\mssql7\data\master.mdf
All messages will now appear in the console screen where SQL Server was started and in the error log.
Deadlocking can also occur when using distributed transactions. For information about resolving deadlocks with distributed transactions, see SQL Server Books Online.
Blocking
An unavoidable characteristic of any lock-based concurrent system is that blocking may occur under some conditions. Blocking happens when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to either wait or block on the first.
In this discussion, the term "connection" refers to a single logged-on session of the database. Each connection appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense. Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
To eliminate deadlocking or blocking problems, contact your system administrator. The system administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of sysprocesses to see what activities each SPID is performing.
Troubleshooting SQL Server Services Accounts
If you have difficulty starting either the MSSQLServer or SQLServerAgent service under a particular user account, you can:
Use Windows NT User Manager to verify that the account has Log on as a service rights on the computer. (Both of these must be assigned within the security context of the local computer, not the domain.)
If services are started by someone who is not a member of the Windows NT local administrators group, the service account must have these permissions:
Full control of the main Microsoft SQL Server directory (by default, \Mssql7).
Full control of the SQL Server database files, regardless of storage location.
The Log on as a service right. Ensure that all logon hours are allowed in the Logon Hours dialog box.
Full control of registry keys at and below HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \MSSQLServer.
Selection of the Password Never Expires box.
Full control of registry keys at and below HKEY_LOCAL_MACHINE \SYSTEM \CurrentControlSet \Services \MSSQLServer.
Full control of registry keys at and below HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows NT\CurrentVersion\Perflib.
If the service does not have the appropriate permissions, certain functionality cannot be accomplished. For example, to write to a mail slot, the service must have a Windows NT domain user account, not just local system, with network write privileges. The service must be a Windows NT account with local administrator privileges to:
Create SQL Server Agent CmdExec and ActiveX Script jobs not belonging to members of the sysadmin role.
Use the automatic server restart feature of SQL Server Agent.
Create SQL Server Agent jobs to be run when the server is idle.
For the MSSQLServer service, right-click the server, click Properties, and then click the Security tab. Under Startup service account, enter the appropriate account and password. If the password is incorrect or has changed, the service cannot be started until the correct password is entered.
Caution For the MSDTC service only, use Services in Control Panel to reenter the user account password. If the password is incorrect or has changed, the service cannot be started until the correct password is entered. If necessary, change the account's password using User Manager, and then enter that password for the service using Services in Control Panel.
For the SQLServerAgent service, expand the server, and then expand Management. Right-click SQLServerAgent and click Properties. On the General tab (the default) in the Service startup account section, enter the account and password.
Assign the account experiencing the problem to another service. If you still have difficulty starting the MSSQLServer or SQLServerAgent service under a particular user account, assign that account to another service (for example, the Spooler service) and verify that the service can be started successfully. If not, the account is either not configured properly or cannot be validated by the domain controller (for example, if no domain controller is available).
Troubleshooting Full-Text Search
If you installed the Full-Text Search feature, it runs as the Microsoft Search service (MSSearch) in Control Panel. If Full-Text Search is not working properly, make sure that the Full-Text Search feature was installed and is running. In SQL Server Service Manager, in the Services box, make sure the Microsoft Search option is selected. For more information, see SQL Server Books Online.
MSSearch runs in the context of the local system account. During SQL Server Setup, SQL Server adds itself to MSSearch service as an administrator. All subsequent changes to the MSSQLServer service account must be made through SQL Server Enterprise Manager. While MSSearch service is running, right-click the server, click Properties, and then click the Security tab. Under Startup Service Account, enter the appropriate account and password. If the password is incorrect or has changed, the service cannot be started until the correct password is entered. This approach ensures that any service account change gets updated in the Microsoft Search service.
Caution Do not use Services in Control Panel to change user account information. Changing user account information through Control Panel results in a mismatch of account information between the MSSQLServer and MSSearch services, and causes full-text catalog population and querying to fail.
If a mismatch of user account information between the MSSQLServer and MSSearch services occurs, verify that both the MSSQLServer and MSSearch services are running. Then, launch SQL Server Enterprise Manager. Using the Properties option and the Security tab as described earlier, change the password for the MSSQLServer service. Changing the password for the MSSQLServer service implicitly updates the account information for the MSSearch service.
Before Uninstalling Site Server 3.0
If the computer running Microsoft SQL Server and Full-Text Search also has Microsoft Site Server version 3.0 installed, verify that Site Server 3.0 Service Pack 1 (SP1) is installed before uninstalling Site Server. If this service pack is not applied, Full-Text Search will not work properly after Site Server 3.0 has been uninstalled.
Presence of UNC path in PATH Environment Variable
If your computer running Microsoft Windows NT does have a UNC path specification present in the system PATH variable or in the user PATH variable, you may experience problems with full-text indexing and Full-Text Search. First, double-check whether you have a UNC path specification in your PATH variable. To do this, issue echo %path% from the command prompt. If you have a UNC path specification, you will see one or more paths of the form \\ computer_name \ share_name.
The symptoms of these problems may include one or more of the following:
When attempting to issue a full-text query, you receive an error message stating "Full-text query failed because full-text catalog '<your_fulltext_catalog_name>' is not yet ready for queries."
The Item count, Catalog size, and Unique word count options (located in the Properties dialog box in SQL Server Enterprise Manager) are all set to 0, even though you populated this full-text catalog.
The Windows NT application log has "stop event" items logged, where Source is Microsoft Search and Category is Indexer. In addition, one of these error event items contains the following error message:
"Unable to load DLL <X:\Program Files\Common Files\system\ContentIndex\ontciutl.dll>. Error 5 - Access is denied."
Note X refers to the drive letter where your Program Files\Common Files directory resides on your computer.
Two possible solutions to this problem are:
If possible, remove the UNC path specification(s) in the SYSTEM and USER PATH variables. Replace these UNC path(s) with remapped drive(s).
Add the location \%SYSTEMDRIVE%\Program Files\Common Files\SYSTEM\ContentIndex in front of any UNC path specification in the SYSTEM path.
Note After making any of these changes, restart your computer. SYSTEM path changes do not take effect until the computer is restarted.
If you have not attempted to repopulate your full-text catalog(s) after the addition of the UNC path specification(s) in your user or system PATH variables, you can use Full-Text Search. If you have attempted repopulation, you must rebuild your full-text catalog(s) and then repopulate them before you can issue full-text queries.
Troubleshooting Multiserver Jobs
When using multiserver jobs, you may experience one or more of these problems.
The job will not download.
Check the download list at the master server (MSX) either through the user interface or by executing sp_help_downloadlist.
Check the SQL Server Agent error log at the target server (TSX).
After resolving the problem, clear the blocking error at the MSX with the user interface.
There are problems with enlisting or defecting.
Verify that an operator named MSXOperator exists at the MSX.
Verify that the MSX is running Mixed Mode Security (SQL Server Authentication and Windows NT Authentication).
Verify that the MSX is running Microsoft Windows NT, not Microsoft Windows 95 or Windows 98 operating systems.
Check named pipes connectivity using makepipe at the MSX and readpipe at the TSX.
What if a TSX is reinstalled while it is still enlisted?
The uninstall portion of SQL Server Setup issues a warning that the server should be defected before uninstalling the TSX. If the warning is ignored, or the warning is noted but the MSX is unavailable when the defection occurs, it becomes necessary to resolve the dangling enlistment at the MSX.
To resolve the dangling enlistment
- At the MSX, execute:
EXECUTE sp_delete_targetserver @server_name = '<TSX server name>', @post_defection = 0
What if I need to reinstall msdb on a TSX server?
Either a backup of msdb exists and the msdb database is successfully restored or the msdb database is damaged and no backup is available.
If a backup of msdb is available and msdb is successfully restored, MSX jobs that have been deleted after the msdb backup may also have been restored. To ensure that the deleted jobs are removed, delete all jobs that originated from the MSX and repost the jobs.
To delete and repost jobs
At the MSX, execute:
EXECUTE sp_resync_targetserver '<server name>'
Or
- In SQL Server Enterprise Manager, in the Multi Server Job Execution Status dialog box, click Synchronize Jobs.
If the msdb database is damaged and no backup is available, the TSX must be reenlisted.
To reenlist the TSX
At the MSX:
All jobs that were targeted at the reenlisted TSX must be retargeted using sp_add_jobserver. To generate the Transact-SQL statements to retarget jobs using sp_add_jobserver, execute sp_generate_target_server_job_assignment_sql and save the result set:
EXECUTE sp_generate_target_server_job_assignment_sql
'<TSX server name>'
At the TSX, execute:
EXECUTE sp_msx_enlist '<MSX server name>'
- At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.
What if I need to restore a backup of msdb on the MSX?
If possible, for all TSX servers that have enlisted after the last backup:
At the MSX, execute sp_generate_target_server_job_assignment_sql and save the result set:
EXECUTE sp_generate_target_server_job_assignment_sql
'<TSX server name>'
- At the MSX, restore the msdb database from a backup. Any TSX that has defected after the last msdb backup must be defected manually.
To defect a TSX server manually
At the MSX, execute:
EXECUTE sp_delete_targetserver @server_name = '<TSX server name>, @post_defection = 0
Any TSX that has enlisted after the last msdb backup must then be reenlisted.
To reenlist a TSX that was enlisted after the last msdb backup
At the TSX, execute:
EXECUTE sp_msx_defect
EXECUTE sp_msx_enlist '<MSX server name>'
- At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.
Note If a defected server is not reenlisted, it will generate many "Incomplete enlistment" errors in the SQL Server Agent error log of the TSX.
If jobs or job assignments have been added, deleted, or changed after the last msdb backup, all remaining TSX servers must be resynchronized.
To resynchronize TSX servers after the last msdb backup
Execute:
EXECUTE sp_resync_targetserver 'ALL'
This EXECUTE statement causes each TSX to delete all its MSX jobs and then download them again. This is a very expensive command. An alternative to executing sp_resync_targetserver with the ALL option is to resynchronize the TSX servers manually by selectively posting instructions for all the changes. This approach may work if only a very few changes occurred. However, it is recommended that sp_resync_targetserver with the ALL option be used to resynchronize TSX servers.
What if a TSX computer is renamed while enlisted?
The TSX server can be defected from the TSX side, but must be defected manually from the MSX side. Jobs that were targeted at the TSX must be retargeted by executing sp_add_jobserver. To generate the Transact-SQL statements to retarget jobs using sp_add_jobserver, execute sp_generate_target_server_job_assignment_sql.
At the MSX, execute sp_generate_target_server_job_assignment_sql and save the result set:
EXECUTE sp_generate_target_server_job_assignment_sql
'<Old TSX server name>', '<New TSX server name>'
At the TSX, execute:
EXECUTE sp_msx_defect
At the MSX, execute:
EXECUTE sp_delete_targetserver
@server_name = '<Old TSX server name>', @post_defection = 0
At the TSX, back up the msdb database. Then, uninstall and reinstall SQL Server.
Important Uninstalling and reinstalling SQL Server is necessary because SQL Server does not support renaming of the computer.
Restore the backup of the msdb database.
At the TSX, execute:
EXECUTE sp_msx_enlist '<MSX server name>'
- At the MSX, execute the previously saved result set of sp_generate_target_server_job_assignment_sql.
Troubleshooting the Operating System
When installing Microsoft SQL Server on a Microsoft Windows NTFS partition, make sure that the NTFS file permissions allow read/write access. Otherwise, this error message may appear in the Microsoft Windows NT application log (for each installation attempt):
Msg 17050: initerrlog: Could not open error log file 'C:\MSSQL7\log\ERRORLOG'. Operating system error =5(Access is denied.).
Verify that all system requirements are met, including installation of Microsoft Windows NT Service Pack 4 (SP4). For more information about system requirements, see SQL Server Books Online.
Internet Connection Dialog Box at Startup
If remote connections are enabled in Microsoft Windows 95 or Windows 98 operating systems, the system may initiate an Internet connection at Windows startup or at the start of many applications. This behavior is sometimes called autodial or autoconnect. This behavior can be disabled by setting the registry key EnableRemoteConnect to N.
To do this, create a text file named DisbleAutoConnect.reg with the following three lines:
REGEDIT4 [HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \OLE] "EnableRemoteConnect"="N"
Changing this setting to disable remote connections should not prevent any of your usual Internet activities. This setting is the default for most systems. However, enabling remote connections is necessary for some features of DCOM. For more information, see https://support.microsoft.com/ .
If there are instances when you need to have remote connections enabled, create a second REG file, named EnableRemoteConnect.reg, with the following three lines:
REGEDIT4 [HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \OLE] "EnableRemoteConnect"="Y"
Run EnableAutoConnect.reg to make the setting, then run DisbleAutoConnect.reg to disable it when autoconnect is no longer needed.
Thread Pooling
The Microsoft SQL Server error log may display the message:
The working thread limit of 255 has been reached
This message is an informational message and does not indicate any problem with the system.
SQL Server maintains a pool of operating system threads for executing batches of SQL statements as they arrive from clients. On Microsoft Windows NT, if the server lightweight pooling configuration option is set to 1, SQL Server maintains a pool of fibers instead of threads; fibers use fewer resources than threads. Using a pool of threads or fibers allows SQL Server to optimize the allocation of processing time when executing multiple SQL statements at the same time. The threads or fibers in this pool are known collectively as the worker threads. For more information, see SQL Server Books Online.
The number of worker threads is controlled by the max worker threads server configuration option. The default is 255 and rarely needs to be changed.
When a batch of Transact-SQL statements is received from a client, if an existing worker thread is free, it is allocated to execute the batch. If no existing worker threads are free and the number of worker threads is less than max worker threads, a new worker thread is allocated. If no worker threads are free and max worker threads is reached, the new batch waits until an existing worker thread completes its current batch and becomes free. When the number of worker threads reaches max worker threads, SQL Server displays this message:
The working thread limit of 255 has been reached
Having all worker threads allocated does not mean that the performance of SQL Server will degrade. Typically, a new batch has only a short wait for a free thread. Allocating more threads may degrade performance because of the increased work required to coordinate resources among the threads. Many SQL Server systems running in production reach this state and run with very high performance levels.
Insufficient Virtual Memory on the Server
When the applications running on a server request more memory than is available on the server, Microsoft Windows opens the Server Process - Out of Virtual Memory dialog box with the following text:
Your system is running low on virtual memory. Please close some applications. You can then start the System option in the Control Panel and choose the Virtual Memory button to create an additional paging file or increase the size of your current paging file.
Use Virtual Memory in Control Panel to make sure that the amount of virtual memory at least 1.5 times the amount of physical memory available on the server. Microsoft SQL Server dynamically requests or frees memory as needed on Microsoft Windows NT systems. SQL Server should not cause this error on Windows NT when running with the default configuration options. For more information, see SQL Server Books Online.
If the virtual memory setting seems appropriate, consider the following actions:
Check that the SQL Server max server memory and min server memory configuration options are not set high enough to use most of the virtual memory. For more information, see SQL Server Books Online.
Check that other applications on the server are not using the available virtual memory.
Use the max server memory and min server memory configuration options to control the amount of memory requested by SQL Server.
Insufficient Resource Space
If a Microsoft SQL Server configuration option is set too high for the amount of available resources, SQL Server fails to start. For example, if the max server memory setting is too high, other applications may take some time to start.
Reset configuration options to their default values as described in SQL Server Books Online, or start SQL Server with minimal configuration by using the -f startup option of the sqlservr application.
Determining When SQL Server Causes a Windows NT Blue Screen
Infrequently, Microsoft Windows NT may either halt with a STOP screen or the console may become completely frozen and unresponsive. This is commonly called a blue screen. This may sometimes happen on a computer where Microsoft SQL Server is running, or may coincide with a particular SQL Server operation such as the bcp utility, a long-running query, and so on.
The vast majority of time, this indicates an operating system, device driver, or hardware problem and should be pursued as such. The Windows NT user or kernel mode process isolation ensures that a user mode application problem does not cause the operating system to stop responding. This section discusses exceptions to this and ways to determine whether to troubleshoot the problem at the system or application layer.
Sometimes the cause of a computer failing to respond or blue screen may be a nonmaskable interrupt (NMI) error. This is sometimes visible as an error code stating NMI, parity check, or I/O parity check. NMI errors are almost always hardware. Usually they are caused by a memory failure; however, they can originate in other hardware subsystems such as video boards. Even if the NMI error happens only during certain SQL Server operations, and if the system passes initial hardware diagnostics, it should still be considered a hardware problem and pursued as such. It may be necessary to use a dedicated memory SIMM testing device, which can often find a transient memory error that eludes software-based diagnostics.
Processes exist on Windows NT in either user mode or kernel mode (sometimes called supervisor or privileged mode). In the Intel x86 architecture, user mode maps to ring 3 and kernel mode to ring 0 of the 4-ring protection system. The x86 architecture has been carried forward with little change in all Intel and compatible processors to date, including the Pentium Pro and Pentium II. Processors such as the Alpha AXP typically have unprivileged and privileged modes as well.
Kernel mode is a privileged processor mode in which a thread has access to system-wide memory (including that of all user-mode processes) and to hardware. By contrast, user mode is a nonprivileged processor mode in which a thread can only access system resources by calling system services.
A user mode process cannot access kernel mode memory, or access memory of another user mode process. This is enforced by processor hardware, in conjunction with kernel mode data structures such as Page Tables. For more information, see the 80386 Programmer's Reference Manual, the 80386 System Software Writer's Guide, or equivalent Alpha AXP documentation.
As a result of this protection system, a user mode application generally cannot stop responding, cause a blue screen, or otherwise cause a failure in the Windows NT operating system. Such problems should be pursued primarily at the system layer as an operating system, device driver, or hardware issue.
Although an application error cannot cause a failure in the operating system, an operating system error can cause an application to stop responding. This is because of the general rule: applications must call inward (to kernel mode), but the operating system can reference outward to user mode freely at any time. A microkernel-influenced architecture such as Windows NT may in turn dispatch certain work to a user-mode system process rather than perform the work in kernel mode. However, the overall principal remains the same: processor hardware enforces process-context isolation, which prevents one process from causing a failure in another, whether one or both are in user mode.
If a user mode application passes an invalid parameter in a Win32 API call, it is the operating system's responsibility to validate this parameter. In very rare cases, passing an invalid parameter may cause a Windows NT blue screen error. However, this is an operating system issue, and should be debugged and pursued as such.
There are a few narrow exceptions to the above guidelines. These exceptions can be easily and quickly eliminated.
Winlogon Problem Caused by SQL Extensible Performance Counters
Current Microsoft Windows NT architecture stipulates that any extensible performance counters added by a service will run in the process context of the Windows NT Winlogon process. Because Winlogon is a vital component of the operating system, a bug or resource leak in any performance counter DLL may disrupt Winlogon, and hence the operating system. The Microsoft SQL Server extensible performance counter DLL is called Sqlctr70.dll, and it exports several SQL Server-specific objects from SQL Server to the operating system. You can use Windows NT Performance Monitor to monitor these objects.
Although it is very rare for Sqlctr70.dll to cause a Winlogon problem, you can find problems more quickly when pursuing a Windows NT failure or blue screen problem on a computer running SQL Server by renaming this DLL. This eliminates the use of SQL Server performance counters; however, you can still use Windows NT Performance Monitor to monitor SQL Server by using regular Windows NT performance counters (such as threads, process, memory, and so on).
If renaming Sqlctr70.dll fixes the problem, and if this is confirmed by reinstating and removing the DLL several times, the problem should be pursued as a SQL Server issue. Otherwise, it should be pursued as a system layer issue.
Resource Leak
If a resource leak continues for a long period of time, the operating system should return the appropriate return code to the application, which should log this. For example, if you receive operating system error 8, "Not enough storage," the operating system should handle the situation by not granting further resource requests. However, a continued application resource leak may not be handled by the operating system under all conditions, resulting in a blue screen or operating system or application failure.
Almost all resource leaks will manifest themselves as a gradual increase in consumption of some resource, such as handles, virtual memory, private bytes, and so on. Therefore, the easiest way to rule in or out a resource leak is to run Windows NT Performance Monitor and log all objects to a file. When the problem occurs, examine the logged performance data for signs of a leak. Some good counters to examine are: handle count, page file bytes, pool paged bytes, pool nonpaged bytes, private bytes, thread count, virtual bytes, and working set for each process running on the computer.
It is not necessary to classify certain values as normal or abnormal. Focus on identifying leaks by the continuous nature of the increase, not by the absolute value at a given time. Remember it is normal for the Windows NT Performance Monitor private bytes counter for Microsoft SQL Server to start well below the configured min server memory setting value, and then increase with activity until it roughly approaches, but does not significantly exceed, that value.
If one of the logged Windows NT Performance Monitor counters continuously increases for the Sqlservr.exe process, and if reaching a certain value repeatedly coincides with a Microsoft Windows NT blue screen or operating system failure, it should be pursued temporarily as a Microsoft Windows operating system issue until the cause of the continuous SQL Server resource leak is understood. Otherwise, it should be pursued as a system layer problem.
CPU Monopolization
If a process spawns high priority threads that are continuously in a runnable state, this process can dominate the computer and prevent the operating system from running. A properly configured Microsoft SQL Server computer will not cause this problem. However, under some conditions, the operating system may appear to stop responding. For example, boosting the priority boost too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other Microsoft Windows NT tasks on the server. In general, you should leave priority boost setting at the default.
When pursuing an operating system failure, verify that the SQL Server configuration settings mentioned above are at their default values. Then, if the operating system or application failure recurs, it should be pursued as a system layer problem.
Troubleshooting Replication
Replication is a complex process, and although Microsoft SQL Server is designed to detect and correct problems automatically, you may encounter problems when implementing your application. Most replication problems can be resolved by following a general troubleshooting approach. Other problems require a specific set of instructions.
General Approach to Replication Troubleshooting
You can begin troubleshooting replication problems by viewing the task history to determine which task failed and the reason for failure. Message details cannot always identify the problem, but can often provide an indicator of the issues, for example, connectivity problems, permissions restrictions, log full errors, and so on.
You can also begin troubleshooting using Replication Monitor to view the status of replication agents:
In Replication Monitor, right-click the distribution agent serving the subscriber, then click Agent Properties. This allows you to view the job properties.
Click the Steps tab, then double-click Run Replication Agent.
Cut the string from the command window.
Paste the string into a console window, with Distrib.exe at the beginning, and with an extra parameter -Output.
Review the output for indications of the problem.
You can increase the amount of history logged by a replication agent and use the additional information to get a more detailed understanding of actions and failures at each point in the replication process. Set the HistoryVerboseLevel to its highest value in the replication agent profile.
If you suspect there is a problem with data consistency in your application, use sp_table_validation to test for row count or checksum differences.
If you encounter one of the following problems, follow the procedures described. For current information about SQL Server replication and suggested solutions to problems, see https://www.microsoft.com/sql/ and https://support.microsoft.com/.
Cannot Start a Replication Agent
A replication agent may not start because SQLServerAgent always calls the xp_logininfo stored procedure to validate that you still belong to your Microsoft Windows NT user groups and to verify your login permissions to the server. The called stored procedure always makes a round trip to the domain controller to do this work. If the agent will not start, an error is returned. This error shows only in the Jobs folder. It never gets propagated to the monitoring node because the agents never actually run, so no notification can be returned. The workaround for the agent not starting is to use standard security or a local computer login as the owner for your jobs.
Cannot Start Another Replication Job
Some replication agents allow only one instance of a particular job to run at a time, for example, one log reader per publication database, one Distributor or Merge Agent per publication/Subscriber pair, one snapshot per publication, and so on. If these jobs fail due to connection failure, it is possible you will not be able to start another job until the network connection time-out is reached or you kill the system process ID (SPID) of the failed job.
Cannot Find Conflicts
If you receive a message that conflicts occurred during the merge process, you can use Replication Conflict Viewer to review the outcomes of the conflicts and to make changes to these outcomes. Make sure you connect to the correct server to view the conflicts. The location of the conflict table varies depending upon whether replication has been configured for centralized or decentralized logging of conflicts. Conflict reporting is usually centralized (the default). If centralized, the conflict table is stored at the Publisher and you must connect to the Publisher to view the conflicts. If decentralized, the conflict table is stored at either the Publisher or the Subscriber, depending upon who lost the conflicts.
Access Denied Reading or Writing Snapshot Files
If you cannot start Snapshot Agent, and receive an "Access Denied" message, run Dcomcnfg.exe. Click the Default Security tab, then make sure the Windows NT account that SQL Server Agent runs under is enabled to have default access and launch permissions.
If the replication agents cannot access the snapshot folder on the Distributor, make sure that the folder is shared correctly. On a Distributor server running Windows NT, the snapshot folder defaults to using the <drive>$ share and a path of \\<computer>\<drive>$\Mssql7\Repldata.
On a Distributor server running the Microsoft Windows 95 or Windows 98 operating systems, the snapshot folder defaults to using the <drive> without a share and a path of <drive>:\Mssql7\Repldata. If your application requires the ability to create pull subscriptions on a server running the Windows 95 or Windows 98 operating systems, you must change the snapshot folder to a network path accessible by replication agents running at the Publisher and Subscribers. You can change the local path to a network path by sharing the folder manually.
Replicating from SQL Server 7.0 to SQL Server 6.5
If you are attempting to replicate from SQL Server 7.0 to SQL Server 6.5 and receive an error message that MSreplication_ subscripions is an invalid object error, try the following procedures:
Run Replp70.sql at the SQL Server 6.5 Subscriber.
Run sp_addpublisher at the Subscriber. Make sure you have the syntax correct:
sp_addpublisher 70 <publisher server name>, <NT account used by
Dist. agent>
Register the SQL Server 6.5 Subscriber at the SQL Server 7.0 Enterprise Manager.
Create a publication at the SQL Server 7.0 Publisher and perform a push subscription to the SQL Server 6.5 Publisher.
For more information about replicating between different versions of SQL Server, see SQL Server Books Online.
Merge Agent or Distribution Agent Fails on Time-out
If the Merge Agent or Distribution Agent fails because of a time-out, increase the QueryTimeout value in the Merge Agent or Distribution Agent profile.
Merge Agent Fails While Enumerating Deletions at the Subscriber
If the Merge Agent fails while enumerating deletions at the Subscriber, examine the error details. This condition is often the result of a large number of deletes to process and a small QueryTimeout value. Increase the QueryTimeout value in the Merge Agent profile.
Errors Occur Applying Constraints During the Initialization of a Partitioned, Merge Publication at a Subscriber.
If errors occur when the snapshot of a partitioned merge publication is applied at a Subscriber, the publication filter may not be defining all of the data needed at the Subscriber to support the constraints referenced at the Subscriber. For example, if you have an EMPLOYEES table that contains a self-referencing constraint on the EMPLOYEES.SUPERVISOR_ID column, make sure your partitioned data set includes all of the supervisors for the employees in the partition as well.
Conflicts Occur When Merging Newly Inserted Rows That Contain Identity Columns
If conflicts occur when merging newly inserted rows that contain identity columns, use the Replication Conflict Viewer to determine the cause of the conflict. An insert conflict is usually caused by inconsistent enforcement of constraints between Publishers and Subscribers. Also, identity columns must be used with caution. You must assign each Subscriber that will insert new rows containing an identity a unique range of identity values.
Data Validation Appears to Fail
Data validation uses rowcounts and checksums to determine if data at the Subscriber has diverged from data at the Publisher. However, there are several conditions other than actual data divergence that could cause data validation to fail.
Checksum computes a 32-bit cyclic redundancy check (CRC) on the entire row image on the page. It does not check columns selectively and cannot operate on a view or vertical partition of the table. Also, the checksum skips the contents of text and image columns (by design).
When doing a checksum, the structure of the table must be identical between the two servers; that is, the tables must have the same columns created and existing in the same order, same data types and lengths, and same NULL/NOT NULL conditions. For example, if the Publisher did a CREATE TABLE, then an ALTER TABLE to add columns, but the script applied at the Publisher is a simple CREATE table, the structure is NOT the same. If you are not certain that the structure of the two tables is identical, look at syscolumns and confirm that the offset in each table is the same.
Floating point values are likely to generate checksum differences if character-mode bcp was used, which is the case if the publication has heterogeneous subscribers. These are due to minor and unavoidable differences in precision when doing conversion to and from character mode.
The custom resolver may not ensure data convergence because it does not handle all conflicts. For example, the custom resolver may only resolve downloads to the Subscriber and not uploads to the Publisher.
If you suspect something is wrong, first check conflict tables and error tables for possible explanations. For example, if the Subscriber database or log is full, SQL Server may be unable to download all of the changes. SQL Server would log an error in the conflict table at the Publisher indicating that not all updates were processed at the Subscriber.
Note You cannot use OPENROWSET on a server running Windows NT Authentication only.
For example, the following FULL OPEN JOIN query uses OPENROWSET to identify differences between two tables.
SELECT authors.au_id AS Local_PK, remote.au_id AS Remote_PK,
(getchecksum(authors.au_id,1),0) AS Local_chksum,remchksum
FROM authors
FULL OUTER JOIN
OPENROWSET('SQLOLEDB','RONSOU4';'sa';'',"SELECT
au_id,(getchecksum(NULL,1),0) AS remchksum FROM pubs.dbo.authors" ) AS
remote
ON (authors.au_id=remote.au_id)
WHERE
-- Find rows with same primary key but different checksums
(getchecksum(authors.au_id,1),0) <> remchksum
OR
-- Find rows which do not exist on one side or the other
authors.au_id IS NULL OR remote.au_id IS NULL
If the query returns rowcount differences, comment out the checksums. The checksums are unnecessary at that point and are more resource intensive than rowcounts to execute.
For more information on data validation, see SQL Server Books Online.
Troubleshooting Statistics
Here are some problems you may encounter when using statistics:
When a table has a large number of indexes, and statistics are being either created or updated, you may receive this error:
Cannot create more than 250 indexes on a table.
Columns larger than 900 bytes cannot have statistics created on them.
Verify that the query is using indexes by viewing the showplan output.
Indexes are ignored. Verify the statistics information by executing DBCC SHOW_STATISTICS.
Verify that there are no distribution page references in sysindexes.
SQL Server Tools Troubleshooting
This section contains information about troubleshooting problems you may encounter when using other tools with Microsoft SQL Server.
Troubleshooting SQL Mail with Exchange Server
Typically, errors in starting a SQL Mail session or sending mail from SQL Mail with Microsoft Exchange Server fall into two categories: permissions problems and Exchange client setup problems. For more information about SQL Mail, see SQL Server Books Online.
Examine this list of items, in this order:
Log on to Microsoft Windows NT with the user account that will be used for the MSSQLServer service. This user account must be an administrator of the local computer and a domain account.
Confirm that the Exchange Server client, Exchnge32.exe, or the Microsoft Outlook client, Outlook.exe, can connect to Exchange Server and that e-mail can be sent.
Confirm that the Exchange Server profile used does not have a Personal Message Store (.pst).
On the Services tab, confirm that the only services available are Microsoft Exchange Server and Personal Address Book, and then click the Delivery tab. Confirm that the selection in the Deliver To box is the mailbox on Exchange Server, which should have a name similar to "Mailbox - <Friendly User Name>" (where <Friendly User Name> is the name of the user who logged on to Windows NT in Step 1).
To run SQL Mail with Exchange Server, the MSSQLServer service must be run under the same user account that logged on in Step 1. In Control Panel, double-click Services, select the MSSQLServer service, and then click Startup.
Confirm that the SQL Mail profile is correct. In SQL Server Enterprise Manager, expand the server, expand the Support Services folder, select SQL Mail, and then right-click. Click Properties, and then on the General tab, verify that the profile name specified in the Profile name box is correct. If needed, click Test. The profile name must match the profile name used in Step 3.
Test Microsoft SQL Server access permissions to Exchange Server by executing xp_cmdshell, which executes with the same permissions as SQL Mail. Use this command to test connectivity to the server, assuming Exchange Server is located on a computer named "NTServer".
xp_cmdshell "NET USE \\NTServer\IPC$"
If this command fails, Step 3 was not completed correctly.
It should now be possible to start SQL Mail either automatically or manually using xp_startmail. When using xp_startmail, the profile can be replaced by replacing <Profile name> below with the profile name used in Step 3:
xp_startmail '<Profile name>',''
Troubleshooting SQL Server Profiler
Here are some problems you may encounter when using SQL Server Profiler:
If you do not see all SQL Server Profiler event classes on the Events tab of the trace definition property sheet, on the Tools menu, click Options. On the General tab, under Events, select All event classes.
If you do not see all SQL Server Profiler data columns on the Data Columns tab of the trace definition property sheet, on the Tools menu, click Options. On the General tab, under Events, select All data columns.
SQL Server Profiler can display the object name (instead of the object ID) if the Server Name and Database ID data columns also appear in your trace.
When setting filters, a blank include filter includes all items in the SQL Server Profiler output. A filter on a data column is not applied to event classes that do not populate that data column.
Because the SQL Server Profiler Extended Stored Procedures save trace queue definitions on the server rather than on the client, SQL Server Profiler is unable to edit or start a trace created originally with the extended stored procedures.
For security reasons, batches containing stored procedures with password arguments are not traced. Instead, an event is produced, which replaces the batch text with a comment.
In Microsoft Windows 95 or Windows 98 operating systems, SQL Server Profiler does not accept client configuration changes until the SQL Server Profiler is closed and restarted.
Due to file locking incompatibilities, Microsoft Windows NT cannot open trace or script files in a Windows 95 or Windows 98 shared directory.
SQL Server Profiler can incur problems accessing files on a remote computer if those files become unavailable.
Here are some common problems you may encounter when replaying a SQL Server Profiler trace:
Replay errors may occur when logins and users captured in the trace do not exist in the target database. If the logins and users exist in the database, they must have the same permissions as they did in the source (traced) database, and they must have the same password as the SQL Server Profiler user replaying the trace. For security reasons, Microsoft Windows NT authenticated logins cannot be impersonated.
Replay errors may occur when the database ID (DBID) of the target database is different from the DBID captured in the trace. To correct this problem, restore a backup of the master database of the source (traced) server onto the target server. Then, restore the user database or databases. As an alternative, the DBID data column can be removed from the trace and the default database set to the target database for each user captured in the trace.
Replay errors may occur when attempting to replay a trace against a database if it is in a different state than the source (traced) database. Updates may fail if data is missing or changed.
Unexpected results may be returned, or replay errors may occur, if replaying a trace containing Session events (Connect, Disconnect, and Existing Connection, for example) and the Binary Data column has not been captured. For Session event classes, the Binary Data column contains information required to set ANSI and quoted identifiers.
System performance may degrade if replaying a trace that contains more concurrent connections than the replay computer can manage. In this case, the trace may be filtered by Application Name, SQL User Name, or another filter if one or more of these data columns were captured in the trace.
Replaying captured events containing the KILL statement may cause unexpected replay results; the SPID that is terminated may not exist or, if it does exist, the SPID may be assigned to a different user or connection than the one traced originally.
When replaying a trace file as fast as possible, SPIDs may become blocked, halting the progress of the replay. To free the blocked SPID and allow the trace to continue, kill the blocking SPID.
Troubleshooting SQL Server Query Analyzer
Here is a problem that you may encounter when using SQL Server Query Analyzer to execute a script:
- Showplan does not return a plan for Transact-SQL statements referencing temporary objects. You can trace the SQL:BatchStarting and Execution Plan events in SQL Server Profiler while executing the Transact-SQL statements to see the plan.
Here are some problems that you may encounter when using SQL Server Query Analyzer to tune a database for a query:
Troubleshooting the Web Assistant Wizard
This section describes how the Web Assistant Wizard handles HTML page generation using the When the SQL Server data changes scheduling option.
With the Web Assistant Wizard, you can generate an HTML file whenever the data changes for one or more tables, by using the Schedule the Web Assistant Job dialog box and selecting When the SQL Server data changes. This is accomplished by building an INSERT, UPDATE, and DELETE trigger for each of the tables selected by the user. Any existing triggers are detected automatically by the Web Assistant Wizard and retained. Additional Transact-SQL statements are appended to the existing trigger code.
The trigger object built by the Web Assistant Wizard will have a name generated according to the following:
Web_tableObjectId_1 -> INSERT trigger Web_tableObjectId_2 -> UPDATE trigger Web_tableObjectId_4 -> DELETE trigger
For example, if the authors table from the pubs database is selected as one of the tables to be considered when the data changes, the three triggers generated by the Web Assistant Wizard will be: Web_16003088_1, Web_16003088_2, and Web_16003088_4 for the INSERT, UPDATE, and DELETE respectively (where 16003088 is the object ID corresponding to the authors table in the pubs database).
sp_depends does not enlist any of the Web Assistant Wizard generated triggers for a given table. You can use sp_helptrigger to return trigger information for the specified table for the current database.
Use the following steps to drop any of the triggers generated by the Web Assistant Wizard:
Identify the object ID for the table in question:
SELECT OBJECT_ID('tabName') tabObjId
List all the triggers for this table object:
USE master
GO SELECT * FROM sysobjects WHERE name LIKE 'Web_tabObjId%' GO
Run the DROP TRIGGER command for each of the triggers you want to drop:
DROP TRIGGER <Webtriggername>