Sysindexes table Cannot Repair

  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    Server: Msg 8965, Level 16, State 1, Line 14

    Table error: Object ID 2. The text, ntext, or image node at page (1:81280), slot 17, text ID 16063266816 is referenced by page (1:6470), slot 5, but was not seen in the scan.

    Server: Msg 8965, Level 16, State 1, Line 14

    Table error: Object ID 2. The text, ntext, or image node at page (1:111072), slot 6, text ID 16063397888 is referenced by page (1:1119), slot 8, but was not seen in the scan.

    Server: Msg 8964, Level 16, State 1, Line 14

    Table error: Object ID 2. The text, ntext, or image node at page (1:114568), slot 4, text ID 16063397888 is not referenced.

    DBCC results for 'sysindexes'.

    The repair level on the DBCC statement caused this repair to be bypassed.

    The system cannot self repair this error.

    The repair level on the DBCC statement caused this repair to be bypassed.

    The repair level on the DBCC statement caused this repair to be bypassed.

    The repair level on the DBCC statement caused this repair to be bypassed.

    There are 2863 rows in 170 pages for object 'sysindexes'.

    CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).

  • Gail Shaw

    SSC Guru

    Points: 1004446

    CheckDB will never repair damage to any of the system tables.

    Can you please run the following and post the entire output?

    DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

    Take a look at this article. http://www.sqlservercentral.com/articles/65804/

    p.s. Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions. This is very important for data corruption where the method of resolving may differ completely between 2000 and 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Paul Randal

    One Orange Chip

    Points: 29438

    These are broken links to statsistics blobs. If you DBCC PAGE the pages referenced in the errors, you'll be able to see what indexes the stats are for. Then try dropping and recreating them.

    CHECKDB never fixes errors in critical system tables, even the newer versions I wrote.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    This results with DBCC Checkdb ('RetailExcel') WITH NO_INFOMSGS, ALL_ERRORMSGS

    Server: Msg 8929, Level 16, State 1, Line 3

    Object ID 2: Errors found in text ID 16063266816 owned by data record identified by RID = (1:1119:6) id = 1860604300 and indid = 3.

    Server: Msg 8929, Level 16, State 1, Line 3

    Object ID 2: Errors found in text ID 16063397888 owned by data record identified by RID = (1:1119:8) id = 1860604300 and indid = 5.

    Server: Msg 8965, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:81280), slot 17, text ID 16063266816 is referenced by page (1:6470), slot 5, but was not seen in the scan.

    Server: Msg 8965, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:111072), slot 6, text ID 16063397888 is referenced by page (1:1119), slot 8, but was not seen in the scan.

    Server: Msg 8964, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:114568), slot 4, text ID 16063397888 is not referenced.

    CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).

    CHECKDB found 0 allocation errors and 5 consistency errors in database 'RetailExcel'.Server: Msg 8929, Level 16, State 1, Line 3

    Object ID 2: Errors found in text ID 16063266816 owned by data record identified by RID = (1:1119:6) id = 1860604300 and indid = 3.

    Server: Msg 8929, Level 16, State 1, Line 3

    Object ID 2: Errors found in text ID 16063397888 owned by data record identified by RID = (1:1119:8) id = 1860604300 and indid = 5.

    Server: Msg 8965, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:81280), slot 17, text ID 16063266816 is referenced by page (1:6470), slot 5, but was not seen in the scan.

    Server: Msg 8965, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:111072), slot 6, text ID 16063397888 is referenced by page (1:1119), slot 8, but was not seen in the scan.

    Server: Msg 8964, Level 16, State 1, Line 3

    Table error: Object ID 2. The text, ntext, or image node at page (1:114568), slot 4, text ID 16063397888 is not referenced.

    CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysindexes' (object ID 2).

    CHECKDB found 0 allocation errors and 5 consistency errors in database 'RetailExcel'.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Query sysindexes and see if these are statistics or indexes

    select OBJECT_NAME(id), name, CASE INDEXPROPERTY(id, name, 'IsStatistics') WHEN 1 THEN 'Statistics' WHEN 0 THEN 'Index' END as Type

    from sysindexes

    WHERE id = 1860604300 AND indid in (3,5)

    If they're indexes, use DROP INDEX to drop them and then recreate it. It they're statistics, just use DROP STATISTICS to drop them.

    Once you've done all that, run checkDB again to see if there are any remaining problems.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    I Got following Results:

    TEMP_COUNTER_WISE_COLLECTION_OFFICE1 _WA_Sys_ORG_CD_6EE6918C Statistics

    TEMP_COUNTER_WISE_COLLECTION_OFFICE1 _WA_Sys_ORGANIZATION_NAME_6EE6918C Statistics

    Last column was statiscs

  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    DROP Statistics not worked. It shows error.

    Pls tell me full query

  • Gail Shaw

    SSC Guru

    Points: 1004446

    amandeep.sandhu (6/24/2009)


    DROP Statistics not worked. It shows error.

    It would kinda help if you said what the error was. I can neither see your screen nor read your mind.

    Pls tell me full query

    As per Books Online:

    DROP STATISTICS <table name>.<statistics name>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    When i tried following it shows me error

    drop table temp_counter_wise_collection_office1

    Server: Msg 7105, Level 22, State 3, Line 10

    Page (1:81280), slot 17 for text, ntext, or image node does not exist.

    Connection Broken

  • Gail Shaw

    SSC Guru

    Points: 1004446

    I didn't say to drop the table. I said try and drop the statistics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    Output of Query Select Object name....... is:

    1. No Column Name | Name | TYPE

    2. TEMP_COUNTER_WISE_COLLECTION_OFFICE1 | _WA_Sys_ORG_CD_6EE6918C | Statistics

    3. TEMP_COUNTER_WISE_COLLECTION_OFFICE1 | _WA_Sys_ORGANIZATION_NAME_6EE6918C | Statistics

    1. Column Name

    2 & 3. Two rows shown with above data.

    I think query wd b

    DROP STATISTICS temp_counter_wise_collection_office1.??

    Wt i write in .Statictics???

    Pls help

  • amandeep.sandhu-1118825

    SSC Veteran

    Points: 275

    Also when i run

    DROP STATISTICS temp_counter_wise_collection_office1._wa_Sys_org_cd_6ee6918c

    Results:

    Server: Msg 7105, Level 22, State 3, Line 7

    Page (1:81280), slot 17 for text, ntext, or image node does not exist.

    Connection Broken

  • Gail Shaw

    SSC Guru

    Points: 1004446

    I was hoping that Paul would comment on this, but he's on vacation for a month.

    If the dropping of the stats doesn't work (and apparently it doesn't, seeing as you got an error trying), then there is no way to fix this error. The only option left is to recreate the database completely.

    Generate scripts of all the objects

    bcp out all the data

    Create a new database

    recreate all the objects

    reload the data

    If you're not entirely sure how to do that, I suggest you get a consultant in to help, or speak to a senior DBA (if there's one there)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • bmannar

    SSChasing Mays

    Points: 649

    Run this...

    checkpoint

    go

    dbcc dropcleanbuffers

    go

    dbcc checkdb ' '

  • Gail Shaw

    SSC Guru

    Points: 1004446

    He already ran CheckDB, See the 4th post in this thread for the results.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply