FIX: Complex Query May Cause 1521 Error

Q109187

The information in this article applies to:
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 query words: 4.20 Windows NT

Keywords :
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2


Last Reviewed: March 24, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.