Post reply

corruption of sys.xml_index_nodes_85575343_32000 object

  • August 21, 2008 at 4:07 pm

    #382353

    My regular DBCC CHECKDB reported following errors:

    DBCC results for 'sys.xml_index_nodes_85575343_32000'.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 213575799, index ID 1, partition ID 72057594057129984, alloc unit ID 72057594043367424 (type LOB data). The off-row data node at page (1:1782), slot 0, text ID 638976000 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 213575799, index ID 1, partition ID 72057594057129984, alloc unit ID 72057594043367424 (type LOB data). The off-row data node at page (1:1788), slot 0, text ID 1461911552 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 213575799, index ID 1, partition ID 72057594057129984, alloc unit ID 72057594043367424 (type LOB data). The off-row data node at page (1:1791), slot 0, text ID 260046848 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    ...

    Table error: Object ID 213575799, index ID 1, partition ID 72057594057129984, alloc unit ID 72057594043367424 (type LOB data). The off-row data node at page (1:1151005), slot 0, text ID 174194688 is not referenced.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 213575799, index ID 1, partition ID 72057594057129984, alloc unit ID 72057594043367424 (type LOB data). The off-row data node at page (1:1151020), slot 0, text ID 876806144 is not referenced.

    There are 19232 rows in 266 pages for object "sys.xml_index_nodes_85575343_32000".

    CHECKDB found 0 allocation errors and 95 consistency errors in table 'sys.xml_index_nodes_85575343_32000' (object ID 213575799).

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (SRWebLog.sys.xml_index_nodes_85575343_32000).

    ...

    It appears to be an internal table. I am not sure what can be done to fix this problem. We don not have backup without DBCC errors. I am afraid the only option is to use repair_allow_data_loss.

    I am not sure how critical corruption on this particular internal table and what the consequences of losing some data on this table are.

    Any help is greatly appreciated.

    Igor

  • August 21, 2008 at 4:18 pm

    #861586

    This table is the internal representation of an XML index. Repair will simply drop and recreate the XML index.

    The most interesting question is why this happened in the first place, and how you should chnage your check/backup strategy so that the next time this happens and it's not redundant info, you don't get real data loss.

    How often do you run DBCC CHECKDB?

    Have you been having any issues with your IO subsystem?

    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

  • August 24, 2008 at 11:25 pm

    #862372

    Hello Paul,

    I have just implemented procedure to perform integrity checks on server in question ;). I am pretty much sure that backups we have contain corrupted information. I guess my only option here would be running DBCC CHECKDB using repair_allow_data_loss option, correct? Are you saying it would be pretty safe to repair this table?

    Besides examining log files, is there anything else I can do to rule out hardware issues? This is 24X7 production server and any distruption (running diagniostic, etc.) is not permissible.

    Thanks,

    Igor

  • August 25, 2008 at 8:37 am

    #862561

    I wouldn't run repair to fix it, as that means the database needs to be in single_user mode. You should just be able to drop and rebuild the XML index manually. Use the sys.internal_tables to figure out which "real" table the XML index is on and then recreate it.

    I'd run a bunch of diagnostics too - memory, IO subsystem, and install and run SQLIOSim.

    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

  • August 25, 2008 at 9:31 am

    #862592

    Thanks a lot Paul!

Viewing 5 posts - 1 through 4 (of 4 total)

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