2005 SP3 CU6 contains a fix for a corruption bug that can happen after rebuilding an XML index that contains XML elements greater than 8000 bytes.
The bug was fixed in 2008 but hadn’t made it back to 2005 until the last CU was released. I had email from someone back in August experiencing corruption and I thought it looked like a bug – so I advised them to call PSS, who told them they were the first to report it on 2005 and it was a bug. Cool! (if you see what I mean).
If you run a DBCC CHECKDB or a DBCC CHECKTABLE after rebuilding an XML index, you’ll see 8964 errors like the one below:
DBCC results for 'sys.xml_index_nodes_980198542_32000'. Msg 8964, Level 16, State 1, Line 2 Table error: Object ID 909246294, index ID 1, partition ID 72057594130530304, alloc unit ID 72057594149535744 (type LOB data). The off-row data node at page (1:621), slot 0, text ID 1302003712 is not referenced.
If the XML index contains XML elements that are larger than 8000 bytes, they will be pushed off-row as row overflow columns, stored in LOB pages. The XML index rebuild isn’t cleaning up its LOB pages properly so leaves orphaned ones lying around. Not really nasty corruption, but enough to set off alarm bells in your monitoring.