consistency error in xml_index_nodes

• Buxton69-502449

  SSCrazy

  Points: 2026

  More actions

  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.

• Paul Randal

  One Orange Chip

  Points: 29544

  More actions

  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

• Buxton69-502449

  SSCrazy

  Points: 2026

  More actions

  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