BUG: Query Tree Grow for System Stored Procedure After Restart

 Last reviewed: April 30, 1997
Article ID: Q122777

The information in this article applies to:
  • Microsoft SQL Server version 4.2bK12 for OS/2
BUG# OS/2: 1891 (4.2bK12)

SYMPTOMS

The query tree for some system stored procedures (for example, sp_help) use temporary tables that may grow after initial execution upon server restart.

Eventually the query tree may grow large enough that execution of the system procedure will result in Msg 703: 

   Memory request failed because more than %d pages are required to
   run the query in its present form. The query should be broken up
   into shorter queries if possible.
This behavior is specific to system stored procedures that use temporary tables for processing: sp_help, sp_helpdevice, and sp_helpdb. Since the query tree grows after the initial execution of the procedure after the server restart, eventually Msg 703 will be encountered.

Since the query trees for many systems procedures are initially small in size, this problem may not be encountered.

WORKAROUND

As described in the SQL "Troubleshooting Guide" on page 30, if Msg 703 is encountered upon execution of a stored procedure, the stored procedure must be dropped and recreated or broken into smaller objects.

Since this problem occurs for system stored procedures created in the INSTMSTR.SQL script (found in the \INSTALL directory), it is recommended to rerun this script to drop or create the stored procedures that are in error.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2bk12. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words:
Keywords : kbother SSrvServer SSrvStProc
Version : 4.2bK12 4.20bK13 4.20bK14 4.20bK
Platform : OS/2
Issue type : kberrmsg

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: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.