INF: Frequently Asked Questions About Microsoft SQL Server

• Microsoft SQL Server, versions 4.2x, 6.0, and 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:

         ARTICLE-ID: Q162361
         Title     : 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.

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:

         ARTICLE-ID: Q132224
         Title     : 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:

         ARTICLE-ID: Q39113
         Title     : 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:

         ARTICLE-ID: Q110139
         Title     : 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:

         ARTICLE-ID: Q110352
         Title     : 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:

         ARTICLE-ID: Q110983
         Title     : 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:

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

10. Q. What are the pricing and licensing terms for SQL Server version 6.5?

    A. For information regarding pricing and licensing for SQL Server
       version 6.5, please see the following Web site:

          http://www.microsoft.com/sql/faq.htm

       For additional information regarding Microsoft SQL Server 6.5,
       please contact your local Microsoft reseller.

11. 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:

          ARTICLE-ID: Q122352
          Title     : INF: Supported Windows NT Versions for SQL Server

12. 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:

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

13. 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:

          ARTICLE-ID: Q163994
          TITLE     : 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.

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

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

16. 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:

          ARTICLE-ID: Q89937
          Title     : INF: Getting Started with Microsoft SQL Server
                      Replication

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