Loading ...
Sorry, an error occurred while loading the content.

Error on dbcc checkdb

Expand Messages
  • Jim Pennington
    All, I m not having any issues with this database in production, but I was testing out some different methods of backup and restores and during the testing I
    Message 1 of 4 , Dec 22, 2011
    • 0 Attachment
      All,

      I'm not having any issues with this database in production, but I was testing out some different methods of backup and restores and during the testing I restored a backup of the database (renamed to TEST) to a non production SQL 2005 server. I can run queries and got no errors, but when I ran 'dbcc checkdb(test) with no_infomsgs, data_purity' I got this reply

      ...
      ...
      Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1380), slot 0, text ID 415105024 is not referenced.

      Msg 8964, Level 16, State 1, Line 1
      Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 0, text ID 115408896 is not referenced.
      Msg 8964, Level 16, State 1, Line 1
      Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 1, text ID 811466752 is not referenced.
      Msg 8964, Level 16, State 1, Line 1
      Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 2, text ID 1226571776 is not referenced.
      Msg 8964, Level 16, State 1, Line 1
      Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 3, text ID 106561536 is not referenced.
      The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.
      CHECKDB found 0 allocation errors and 2357160 consistency errors in table 'sys.xml_index_nodes_2089058478_32000' (object ID 5575058).
      CHECKDB found 0 allocation errors and 2357160 consistency errors in database 'TEST'.
      repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TEST).

      Any suggestions on what to do about this? The database is NOT marked as Suspected.

      Thanks in advanced...

      PS, I did not check to see which service pack the production vs non production SQL was running. Not sure if that would make a difference or not.

      --
      Jim Pennington
    • Jose Chinchilla
      Seems like you have corrupt pages on your index. It the backup came from a production db, the data allocated in this corrupt index pages may be excluded when
      Message 2 of 4 , Dec 23, 2011
      • 0 Attachment
        Seems like you have corrupt pages on your index. It the backup came from a production db, the data allocated in this corrupt index pages may be excluded when they are queried.
         
        I would first try rebuilding the index. If that doesn't work I would also try dropping and re-creating the index.
         
        If problems persists then run DBCC CHECKDB [TESTDB] REPAIR_ALLOW_DATA_LOSS  <-  be aware data *may* be lost. Check Paul Randal's blog http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx
         
        These operations may take a long time depending on the size of your tables and indexes.
         
        Atentamente/Regards,
        Jose R. Chinchilla
         

        To: tampasql@yahoogroups.com
        From: jimmypennington@...
        Date: Thu, 22 Dec 2011 09:33:34 -0500
        Subject: [tampasql] Error on dbcc checkdb

         
        All,

        I'm not having any issues with this database in production, but I was testing out some different methods of backup and restores and during the testing I restored a backup of the database (renamed to TEST) to a non production SQL 2005 server. I can run queries and got no errors, but when I ran 'dbcc checkdb(test) with no_infomsgs, data_purity' I got this reply

        ...
        ...
        Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1380), slot 0, text ID 415105024 is not referenced.

        Msg 8964, Level 16, State 1, Line 1
        Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 0, text ID 115408896 is not referenced.
        Msg 8964, Level 16, State 1, Line 1
        Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 1, text ID 811466752 is not referenced.
        Msg 8964, Level 16, State 1, Line 1
        Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 2, text ID 1226571776 is not referenced.
        Msg 8964, Level 16, State 1, Line 1
        Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 3, text ID 106561536 is not referenced.
        The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.
        CHECKDB found 0 allocation errors and 2357160 consistency errors in table 'sys.xml_index_nodes_2089058478_32000' (object ID 5575058).
        CHECKDB found 0 allocation errors and 2357160 consistency errors in database 'TEST'.
        repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TEST).

        Any suggestions on what to do about this? The database is NOT marked as Suspected.

        Thanks in advanced...

        PS, I did not check to see which service pack the production vs non production SQL was running. Not sure if that would make a difference or not.

        --
        Jim Pennington

      • Jim Pennington
        Thanks for the help, I just found out the issue is a bug in SQL 2005 and was fixed in SP4, I was running SP3. I just upgraded to SP4 and rebuilt the indexes
        Message 3 of 4 , Dec 23, 2011
        • 0 Attachment
          Thanks for the help, I just found out the issue is a bug in SQL 2005 and was fixed in SP4, I was running SP3. I just upgraded to SP4 and rebuilt the indexes and it reports fine. 

          Thanks again for the response. 

          Jim Pennington

          On Dec 23, 2011, at 11:51 AM, Jose Chinchilla <joseraulchinchilla@...> wrote:

           

          Seems like you have corrupt pages on your index. It the backup came from a production db, the data allocated in this corrupt index pages may be excluded when they are queried.
           
          I would first try rebuilding the index. If that doesn't work I would also try dropping and re-creating the index.
           
          If problems persists then run DBCC CHECKDB [TESTDB] REPAIR_ALLOW_DATA_LOSS  <-  be aware data *may* be lost. Check Paul Randal's blog http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx
           
          These operations may take a long time depending on the size of your tables and indexes.
           
          Atentamente/Regards,
          Jose R. Chinchilla
           

          To: tampasql@yahoogroups.com
          From: jimmypennington@...
          Date: Thu, 22 Dec 2011 09:33:34 -0500
          Subject: [tampasql] Error on dbcc checkdb

           
          All,

          I'm not having any issues with this database in production, but I was testing out some different methods of backup and restores and during the testing I restored a backup of the database (renamed to TEST) to a non production SQL 2005 server. I can run queries and got no errors, but when I ran 'dbcc checkdb(test) with no_infomsgs, data_purity' I got this reply

          ...
          ...
          Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1380), slot 0, text ID 415105024 is not referenced.

          Msg 8964, Level 16, State 1, Line 1
          Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 0, text ID 115408896 is not referenced.
          Msg 8964, Level 16, State 1, Line 1
          Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 1, text ID 811466752 is not referenced.
          Msg 8964, Level 16, State 1, Line 1
          Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 2, text ID 1226571776 is not referenced.
          Msg 8964, Level 16, State 1, Line 1
          Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 3, text ID 106561536 is not referenced.
          The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.
          CHECKDB found 0 allocation errors and 2357160 consistency errors in table 'sys.xml_index_nodes_2089058478_32000' (object ID 5575058).
          CHECKDB found 0 allocation errors and 2357160 consistency errors in database 'TEST'.
          repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TEST).

          Any suggestions on what to do about this? The database is NOT marked as Suspected.

          Thanks in advanced...

          PS, I did not check to see which service pack the production vs non production SQL was running. Not sure if that would make a difference or not.

          --
          Jim Pennington

        • Good, Mike
          That s good to know. Thanks for updating us all w/final resolution. From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jim
          Message 4 of 4 , Dec 23, 2011
          • 0 Attachment

            That's good to know.  Thanks for updating us all w/final resolution. 

             


            http://www.hsn.com

            From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jim Pennington
            Sent: Friday, December 23, 2011 12:12
            To: tampasql@yahoogroups.com
            Cc: Tampa SQL Server User Group
            Subject: [EXTERNAL] Re: [tampasql] Error on dbcc checkdbebuilt

             

             

            Thanks for the help, I just found out the issue is a bug in SQL 2005 and was fixed in SP4, I was running SP3. I just upgraded to SP4 and rebuilt the indexes and it reports fine. 

             

            Thanks again for the response. 

            Jim Pennington


            On Dec 23, 2011, at 11:51 AM, Jose Chinchilla <joseraulchinchilla@...> wrote:

             

            Seems like you have corrupt pages on your index. It the backup came from a production db, the data allocated in this corrupt index pages may be excluded when they are queried.
             
            I would first try rebuilding the index. If that doesn't work I would also try dropping and re-creating the index.
             
            If problems persists then run DBCC CHECKDB [TESTDB] REPAIR_ALLOW_DATA_LOSS  <-  be aware data *may* be lost. Check Paul Randal's blog http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx
             
            These operations may take a long time depending on the size of your tables and indexes.
             
            Atentamente/Regards,
            Jose R. Chinchilla
             


            To: tampasql@yahoogroups.com
            From: jimmypennington@...
            Date: Thu, 22 Dec 2011 09:33:34 -0500
            Subject: [tampasql] Error on dbcc checkdb

             

            All,

            I'm not having any issues with this database in production, but I was testing out some different methods of backup and restores and during the testing I restored a backup of the database (renamed to TEST) to a non production SQL 2005 server. I can run queries and got no errors, but when I ran 'dbcc checkdb(test) with no_infomsgs, data_purity' I got this reply

            ...
            ...
            Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1380), slot 0, text ID 415105024 is not referenced.
            Msg 8964, Level 16, State 1, Line 1
            Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 0, text ID 115408896 is not referenced.
            Msg 8964, Level 16, State 1, Line 1
            Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 1, text ID 811466752 is not referenced.
            Msg 8964, Level 16, State 1, Line 1
            Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 2, text ID 1226571776 is not referenced.
            Msg 8964, Level 16, State 1, Line 1
            Table error: Object ID 5575058, index ID 1, partition ID 72057594297778176, alloc unit ID 72057594042646528 (type LOB data). The off-row data node at page (1:1381), slot 3, text ID 106561536 is not referenced.
            The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.
            CHECKDB found 0 allocation errors and 2357160 consistency errors in table 'sys.xml_index_nodes_2089058478_32000' (object ID 5575058).
            CHECKDB found 0 allocation errors and 2357160 consistency errors in database 'TEST'.
            repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TEST).


            Any suggestions on what to do about this? The database is NOT marked as Suspected.

            Thanks in advanced...

            PS, I did not check to see which service pack the production vs non production SQL was running. Not sure if that would make a difference or not.

            --
            Jim Pennington

          Your message has been successfully submitted and would be delivered to recipients shortly.