FIX: Complex Query May Cause 1521 Error

 Last reviewed: December 19, 1997
Article ID: Q109187
4.20 WINDOWS kbbug4.20 kbother kberrmsg kbfix4.21a.sp1

The information in this article applies to:

  • Microsoft SQL Server, version 4.2

BUG# NT: 597 (4.2)

SYMPTOMS

Under certain conditions SQL Server may return error 1521: 

   Sort failed because a table in tempdb used for the processing
   of the query had a bad data page count.
This generally occurs when processing very complex queries that may involve any of the following: multiple tables, the use of dynamic indexes, internal sorting, or heavy use of tempdb.

WORKAROUND

In some cases this problem can be resolved by executing one or more of the following:

  1. Run UPDATE STATISTICS on all the tables involved.

  2. Modify the query so the optimizer can produce a different query plan. - Make sure your indexes have been designed correctly and efficiently. - Purge or archive any unnecessary data from any tables involved. - Change BETWEEN clauses to the corresponding <= and >= expressions.

NOTE: You may review your query plan by running SET SHOWPLAN ON before executing the query. For more information on interpreting SHOWPLAN output refer to "Appendix B: Understanding SHOWPLAN Output" in the "Microsoft SQL Server for NT Troubleshooting Guide."

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. This problem was corrected in the latest U.S. Service Pack for SQL Server version 4.21a. For information on obtaining the Service Pack, please contact your primary support provider.

Additional reference words: 4.20 Windows NT
KBCategory: kbother kberrmsg
KBSubcategory: SSrvWinNT

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.