BUG: Many Objects May Cause Error 703 in DBCC

Last reviewed: April 28, 1997
Article ID: Q88685

The information in this article applies to:
  • Microsoft SQL Server version 4.2 for OS/2
  • Microsoft SQL Server, versions 4.21 and 4.21a
BUG# OS/2: 1491 (4.2)
       NT:  673 (4.2)

SYMPTOMS

A dbcc checkcatalog attempted on a database which has many objects (for example, over 640 tables) may get a 703 error:

   Memory request failed because more than 64 pages are required
   to run the query in its present form.  The query should be broken
   up into shorter queries if possible.

WORKAROUND

The script below will create a stored procedure CatalogCheck which tests most items also tested by dbcc checkcatalog, but will not get the 703 error. The sa can execute the procedure in any database:

   execute master..CatalogCheck

Key the following script into a file NEWCATCK.SQL and execute it:

   isql /Usa /P<password> /S<server> /e /inewcatck.sql
   /onewcatck.rpt

The script is:

create procedure CatalogCheck as

/* Check syscolumns <---> sysobjects on "id"    */
if exists (select *
           from syscolumns
           where id not in (select id
                            from sysobjects))
  print "A column exists in syscolumns with no associated object in
sysobjects"

/* Check syscolumns <---> systypes on "usertype" */
if exists (select *
           from syscolumns
           where usertype not in (select usertype
                                  from systypes))
  print "A column exists in syscolumns with no associated type in systypes"

/* Check sysobjects <---> syscolumns on "id" for views */
if exists (select *
           from sysobjects
           where type in ('v', 'V')
           and id not in (select id
                           from syscolumns))
  print "A view exists in sysobjects with no columns in syscolumns"

/* Check sysobjects <---> sysprocedures on "id" for views */
if exists (select *
           from sysobjects
           where type in ('v', 'V')
           and id not in (select id
                          from sysprocedures))
  print "A view exists in sysobjects with no entry in sysprocedures"

/* Check sysobjects <---> sysprocedures on "id" for
procedures */ if exists (select *
           from sysobjects
           where type in ('p', 'P')
           and id not in (select id
                          from sysprocedures))
  print "A procedure exists in sysobjects with no entry in sysprocedures"

/* Check sysobjects <---> sysprocedures on "id" for rules &
defaults */ if exists (select *
           from sysobjects
           where type in ('d', 'D', 'r', 'R')
           and id not in (select id
                          from sysprocedures))
  print "A rule and/or default exists in sysobjects with no entry in
sysprocedures"

/* Check sysobjects <---> syscolumns on "id" for tables */
if exists (select *
           from sysobjects
           where type in ('s', 'S', 'u', 'U')
           and id not in (select id
                          from syscolumns))
  print "A table exists in sysobjects with no entry in syscolumns"

/* Check sysobjects <---> sysindexes on "id" for tables */
if exists (select *
           from sysobjects
           where type in ('s', 'S', 'u', 'U')
           and id not in (select id
                          from sysindexes))
  print "A table exists in sysobjects with no entry in sysindexes"

/* Check sysindexes <---> sysobjects on "id" */
if exists (select *
           from sysindexes
           where id not in (select id
                            from sysobjects))
  print "An entry exists in sysindexes with no entry in sysobjects"

/* Check sysindexes <---> syssegments on "segment" */
if exists (select *
           from sysindexes
           where segment not in (select segment
                                from syssegments))
  print "An entry in sysindexes has a 'segment' value with no entry in
syssegments"

/* Check sysprocedures <---> sysobjects on "id" */
if exists (select *
           from sysprocedures
           where id not in (select id
                            from sysobjects))
  print "An entry exists in sysprocedures with no entry in sysobjects"

print "Catalog Check complete" go

STATUS

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


Additional query words: sp_configure tempdb Windows NT
Keywords : kbbug4.20 kbprg SSrvGen SSrvWinNT
Version : 4.2 | 4.21 4.21a
Platform : OS/2 WINDOWS
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 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.