INF: Frequently Asked Questions About Microsoft SQL Server

• Microsoft SQL Server versions 4.2x, 6.0, 6.5

SUMMARY

This article contains abstracts of the Frequently Asked Questions received by Microsoft's SQL Server support engineers. For more information on an individual topic, see the full text of the Knowledge Base article referenced in each abstract below.

MORE INFORMATION

1. Q. How can I avoid deadlocks in my application?
   
   A. 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.
   
   For best scalability, performance, and concurrency, application and query design should emphasize keeping the transaction path length short and holding locks as briefly as possible. The foundation of most concurrency problems is laid when the application and database are designed. For this reason, it is critical that these issues be well understood at design time. Otherwise, a hidden performance limitation may be unintentionally engineered into the application, and this may not appear until full-scale stress testing.
   
   For information on identifying and resolving deadlocking problems, see the following article in the Microsoft Knowledge Base:
   

   Q162361 : INF: Understanding and Resolving SQL Server Blocking Problems




2. Q. How can I improve DBCC performance in SQL Server?
   
   A. The database consistency checker (DBCC) utilities are a collection of programs used to verify integrity of a SQL Server database. They are conceptually similar to file system checking programs such as CHKDSK in MS-DOS, Windows 95, and Windows NT, and fsck in UNIX. Like file system checking programs, DBCC can take a significant amount of time to run on large data sets.
   
   For more information about improving DBCC performance, see the following articles in the Microsoft Knowledge Base:
   

   Q134656 : INF: Details and Strategies for Using DBCCs
   
   Q140569 : INF: How to Improve DBCC Performance on SQL Server




3. Q. How does Microsoft SQL Server handle encryption?
   
   A. Microsoft SQL Server versions 6.0 and 6.5 allow for encryption of data "over the wire" for both 16-bit and 32-bit clients with the encryption option of the Multi-Procotol Network Library.
   
   SQL Server relies on the Microsoft Windows NT RPC API to do the encryption of network traffic. Windows NT RPC uses 40-bit RC4 encryption, which is the maximum allowed for export, so there are no differences between the U.S. and International versions.
   
   For more information about this topic, see the following article in the Microsoft Knowledge Base:
   

   Q132224 : INF: Encryption Algorithm in the Multi-Protocol Net Library




4. Q. What causes a discrepancy between the DBCC page count and the count reflected in SYSINDEXES DPAGES?
   
   A. It is not unusual for DBCC to find a discrepancy between the actual page count and the count reflected in SYSINDEXES DPAGES for the SYSLOGS table. This discrepancy occurs because the page count in SYSINDEXES (DPAGES) is not updated every time something is logged; that would cause too much overhead. Instead, the changes are saved until a CHECKPOINT is executed.
   
   The discrepancy does not cause problems because the value in SYSINDEXES is used only for reporting space allocation, not for enforcing it. Also, the occasionally erroneous value in SYSINDEXES never affects the choice of access strategy because queries are never run on SYSLOGS.
   
   For more information about this topic, see the following article in the Microsoft Knowledge Base:
   

   Q39113 : PRB: DBCC Reports Page Count Discrepancy on SYSLOGS Table




5. Q. What are the causes and ramifications of the transaction log filling up?
   
   A. The SQL Server transaction log can become full, which prevents further UPDATE, DELETE, or INSERT activity in the database, including CHECKPOINT.
   
   This is usually seen as error 1105:
   

   Can't allocate space for object syslogs in database dbname because
   the logsegment is full. If you ran out of space in syslogs, dump
   the transaction log. Otherwise use ALTER DATABASE or
   sp_extendsegment to increase the size of the segment.

   This can happen on any database, including master or tempdb.
   
   Several difficult-to-predict factors can account for variation in log space consumption, such as:
   
   
   • A large atomic transaction, especially a bulk update, insert, or delete.
   
   
   
   • An uncommitted transaction.
   
   
   
   • Checkpoint handler truncation bandwidth exceeded.
   
   
   
   • Truncation threshold exceeded.
   
   
   
   • Interactions between any of the previously described conditions.
   
   
   
   • Transactions marked for publication but not read by the log reader.
   
   
   
   For more information on this topic, see the following article in the Microsoft Knowledge Base:
   

   Q110139 : INF: Causes of SQL Transaction Log Filling Up




6. Q. How should performance optimization for SQL Server be approached?
   
   A. To most effectively optimize Microsoft SQL Server performance, you must identify the areas that will yield the largest performance increases over the widest variety of situations, and focus analysis on these areas. Otherwise, you may expend significant time and effort on topics that may not yield sizable improvements.
   
   Experience shows that the greatest benefit in SQL Server performance can be gained from the general areas of logical database design, index design, and query design. Conversely, the biggest performance problems are often caused by deficiencies in these same areas. If performance is a concern, you should concentrate on these areas first, since very large performance improvements can often be achieved with a relatively small time investment.
   
   While other system-level performance issues, such as memory, cache buffers, hardware, and so forth, are certainly candidates for study, experience shows that the performance gain from these areas is often of an incremental nature. SQL Server manages available hardware resources in a largely automatic fashion, reducing the need (and thus, the benefit) of extensive system-level hand-tuning.
   
   For more information about this topic see the following article in the Microsoft Knowledge Base:
   

   Q110352 : INF: Optimizing Microsoft SQL Server Performance




7. Q. What memory allocations are suggested for SQL Server?
   
   A. Microsoft SQL Server allows the use of up to 2,048 MB of virtual memory. Windows NT provides each 32-bit Windows application a 4-gigabyte (GB) virtual address space, the lower 2 GB of which is private per process and available for application use. The upper 2 GB is reserved for system use.
   
   The 4-GB address space is mapped to the available physical memory by the Windows NT Virtual Memory Manager (VMM). The available physical memory can be up to 4 GB, depending on hardware platform support.
   
   A 32-bit Windows application such as SQL Server only perceives virtual or logical addresses, not physical addresses. How much physical memory an application uses at a given time (the working set) is determined by available physical memory and the VMM. The application cannot directly control memory residency.
   
   Virtual address systems such as Windows NT allow the over-committing of virtual memory, such that the ratio of virtual to physical memory exceeds 1:1. As a result, larger programs can run on machines with a variety of physical memory configurations. However, in most cases, using significantly more virtual memory than the combined average working sets of all the processes will result in poor performance.
   
   For more information about this topic, see the following article in the Microsoft Knowledge Base:
   

   Q110983 : INF: Recommended SQL Server for NT Memory Configurations




8. Q. How are TCP/IP and Windows Sockets supported in SQL Server?
   
   A. Microsoft SQL Server versions 6.0 and 5.5 support client communication for Windows- or Windows NT-based clients, using standard Windows Sockets as the IPC method across the TCP/IP protocol. The Windows Sockets Net-Libraries have been extensively tested on the supported platforms for connecting to Microsoft SQL Server. Using these Net-Libraries with other TCP/IP protocols should work if those protocols properly support Windows Sockets. However, their use on these platforms is not guaranteed. The protocol provider should test and state their support policy.
   
   Third party 16-bit TCP/IP products (other than those provided with Windows for Workgroups) which properly support the Windows Sockets specifications should work properly with the Win16 TCP/IP Sockets Net-Library (DBMSSOC3.DLL). Though not officially tested and supported, products that properly implement the specification should work with the Net-Library.
   
   For more information about this topic, see the following article in the Microsoft Knowledge Base:
   

   ARTICLE-ID: Q107647 Title : INF: Connecting to SQL Server from TCP/IP Sockets Clients




9. Q. What causes error messages 17824, 17832, and 1608 to be placed in the error log?
   
   A. A variety of Microsoft SQL Server communication-related errors are possible. In general, these do not indicate a problem with SQL Server, but rather a network, network configuration, or client application problem. On both the client and server sides, SQL Server and its applications mostly exist above the International Organization for Standardization (ISO) network layer. The responsibility for establishing and maintaining a reliable network connection belongs to the network and system layers below SQL Server.
   
   Possible errors include:
   
   

   
         Server-Side Errors
         ------------------
   
         17832    Unable to read login packet(s)
         17825    Unable to close server-side connection
         17824    Unable to write to server-side connection
         10058    Can't send after socket shutdown
         10054    Connection reset by peer
         10053    Software caused connection abort
         1608     A network error was encountered while sending results to the
                  front end
         232      The pipe is being closed
         109      The pipe has been ended
   
         Client-Side Errors
         ------------------
   
         10008    Bad token from SQL Server: datastream processing
                  out of sync
         10010    Read from SQL Server failed
         10018    Error closing network connection
         10025    Write to SQL Server failed
      

   For more information about this topic, refer to SQL Server Books Online, Administrator's Companion, or see the following article in the Microsoft Knowledge Base:
   

   Q109787 Title : INF: SQL Communication Errors 17832, 17824, 1608, 232, and 109




10. Q. On what versions of windows NT are Microsoft SQL Server versions 6.5 and 6.0 supported?
    
    

    Version 6.5

    Microsoft SQL Server version 6.5 is supported on Windows NT Server versions 3.51 and 4.0.
    
    Specific notes on SQL Server 6.5:
    
    
    • SQL Server 6.5 is NOT supported on Windows NT version 3.5.
    
    
    
    • SQL Server 6.5 is supported on Windows NT 4.0.
    
    
    
    
    

    Version 6.0

    Microsoft SQL Server version 6.0 is supported on Windows NT Server versions 3.5 and 3.51.
    
    Specific notes on SQL Server 6.0:
    
    
    • Microsoft SQL Server version 6.0 Is NOT supported on Windows NT version 4.0.
    
    
    
    • Although Microsoft SQL Server version 6.0 is supported on Windows NT version 3.5, the preferred platform is Windows NT version 3.51.
    
    
    
    For more information about this topic, see the following article in the Microsoft Knowledge Base:
    

    Q122352 : INF: Supported Windows NT Versions for SQL Server




11. Q. Why does SQL Server Enterprise Manager (SEM) sometimes show a negative number for the size of a device?
    
    A. This problem occurs if the device being edited is on a drive that has more than 2 gigabytes of free space. When this problem occurs, the size of the device cannot be changed in the dialog box.
    
    To work around this problem, use the DISK RESIZE command to manually increase the size of the device.
    
    For more information about this topic, see the following article in the Microsoft Knowledge Base:
    

    Q135866 : BUG: SEM - Edit Devices Dialog Displays Negative Device Size





12. Q. Can I use the Microsoft Access Upsizing Tools with SQL Server 6.0 and 6.5?
    
    A. The Upsizing Tools (Upsizing Wizard and SQL Server Browser) are available for each version of Microsoft Access but are version specific.
    
    To download the Access for Windows 95 Upsizing Tools, open Article Q149841, "INF: MS Access 95 Upsizing Tools Available on MSL" in the Knowledge Base on the Microsoft Web site, and click on Upsize95.exe. You can use this version of the Upsizing Wizard to move a database from Microsoft Access for Windows 95 to Microsoft SQL Server 6.0 and 6.5.
    
    When using this Upsizing Wizard to move a table to SQL Server 6.5, if timestamp fields are created, they must be populated before Microsoft Access can change data in the table. This is described in the following article in the Microsoft Knowledge Base:
    

    Q163994 : PRB: "Record Has Been Changed" Error After Upsizing

    
    The Upsizing Wizard for Microsoft Access 97 is now available. Download this file by visiting the Microsoft Access Home Page at http://www.microsoft.com/msaccess/. Click on Access Developer Forum to find the current Microsoft Access Upsizing Tools 97 hot link.
    
    The Upsizing Wizard for Microsoft Access 2.0 is intended to move data to Microsoft SQL Server version 4.21. To move a database from Access 2.0 to SQL Server versions 6.0 or 6.5, convert the database to Microsoft Access for Windows 95 and use the Microsoft Access for Windows 95 Upsizing Wizard.



13. Q. What is the Chkupg65.exe Utility that I run before upgrading to SQL Server 6.5 for?
    
    A. Before you begin an upgrade to Microsoft SQL Server version 6.5, run the Chkupg65.exe utility, which checks that database status is acceptable, that all necessary comments exist in syscomments, and that there are no keyword conflicts. Chkupg65.exe writes this information to an output file. Review the report and take any action the report indicates is necessary to prepare the existing installation for an upgrade. For more information about running the Chkupg65.exe utility, see "Running the CHKUPG65.EXE Utility" in SQL Server Books Online. Note that Chkupg65.exe was called Chkupg.exe in earlier versions, but the functionality has not changed.



14. Q. What are the new keywords in SQL Server 6.5?
    
    A. The following is a list of the new keywords:
    
    AUTHORIZE
    CASCADE
    CROSS
    DISTRIBUTED
    ESCAPE
    FULL
    INNER
    JOIN
    LEFT
    OUTER
    PRIVELEGES
    RESTRICT
    RIGHT
    SCHEMA
    WORK



15. Q. How do I install and use Microsoft SQL Server 6.0 replication?
    
    A. The article in the Microsoft Knowledge Base listed below provides a reference for some of the most common issues you can encounter when installing and using Microsoft SQL Server 6.0 replication. This article is not a replacement for the SQL Server 6.0 documentation.
    
    All of the concepts, terms, and topics in the article below are documented in the SQL Server "Administrator's Companion," Part 6, Replication (both in printed and "Books Online" versions of the documentation). This section of the manual is the most complete reference for replication issues, questions, and troubleshooting. Anyone who is planning to install and use SQL Server 6.0 replication should thoroughly read all chapters in this section of the manual.
    
    For more information about this topic, see the following article in the Microsoft Knowledge Base:
    

    Q89937 : INF: Getting Started with Microsoft SQL Server Replication




16. Q. What changes to the SQL Server 6.0 server do I need to make to administer the server using the SQL Server 6.5 Enterprise Manager?
    
    A. Run Sqlole65.sql on the SQL Server 6.0 server. To install Sqlole65.sql, run the script for your platform found on the SQL Server compact disc.

Additional query words: 4.2x sql6 sqlfaqtop

Keywords          : SSrvGen SQLFAQ 
Version           : 4.2x 6.0 6.5
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: April 10, 1999