Post reply

consistency error in xml_index_nodes

  • May 1, 2008 at 7:47 am

    #206827

    Hi, I originally posted this in Administration but never got any replies but I've still got this issue, if anyone can help point me in the right direction or can confirm I will only loose index data it would be of great help.

    Thanks

    .......

    I'm getting a consistency error in xml_index_nodes in a database table after I run CHECKDB, it only started to happen yesterday, if I repair it and it gets rid of data from an internal table like this what would it affect?

    The full error is:

    DBCC results for 'sys.xml_index_nodes_421576540_32000'.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 1813581499, index ID 1, partition ID 72057594098286592, alloc unit ID 72057594104119296 (type LOB data). The off-row data node at page (1:2635768), slot 0, text ID 1229193216 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 1813581499, index ID 1, partition ID 72057594098286592, alloc unit ID 72057594104119296 (type LOB data). The off-row data node at page (1:2635770), slot 1, text ID 775946240 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    .....

    There are 382492705 rows in 2979246 pages for object "sys.xml_index_nodes_421576540_32000".

    CHECKDB found 0 allocation errors and 55 consistency errors in table 'sys.xml_index_nodes_421576540_32000' (object ID 1813581499).

    CHECKDB found 0 allocation errors and 55 consistency errors in database 'dbCPULink'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbCPULink).

    Is there anything I can do appart from a restore which is not possible to avoid data loss and if this data was removed from the internal table what would I be losing?

    Thanks

    ........

    I've found out what causes this error, it's reindexing on a primary XML index.

    I ran repair_allow_data_loss on a backup copy of the database and it fixes the consistency error but then I reindex again and the error comes back, I know what you're thinking, don't reindex it then, I can miss out the index in my reindex job but it still doesn't tell me why it has happened or by fixing the error what data I have lost if any or what I can do to be able to reindex without causing the error.

    Thank you for your help in advance.

  • May 1, 2008 at 12:17 pm

    #809758

    Hi,

    Which version of 2005 are you using? (include hotfix level)

    Can you post the full results of DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS from the restored copy of the backup pre-reindexing and then again AFTER the repair and reindexing? You've either got a corruption in the XML BLOB itself in the table or there's a bug in XML indexing.

    Thanks

    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

  • May 2, 2008 at 1:44 am

    #809948

    It's just a straight SP2 with no hotfixes, I'll run the checkdb today and post it later, it takes a while as the DB is 80Gb.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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