March 20, 2008 at 3:49 am
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
March 25, 2008 at 10:02 am
I'm still having this problem, has anyone got any suggestions?
Thanks
March 25, 2008 at 11:08 am
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.
April 7, 2008 at 7:30 am
If anyone has any ideas or knows where I could look any help would be very welcome, I need to sort this out soon.
Thanks
Buxton69
July 8, 2008 at 9:39 am
have you ever found a resolution for this?
I'm working on the same issue with microsift right now. We recreate the index and everything is fine and then we do the reindex and the errors come back.
If microsoft find something i will post back here.
July 8, 2008 at 9:51 am
you can not do the reindex and reorgnize index at same time.
maybe you could consider to shrinke it first,
then reindex or reorgnize is ok.
July 8, 2008 at 10:26 am
sqlservercenter (7/8/2008)
maybe you could consider to shrinke it first,then reindex or reorgnize is ok.
Don't! Shrink! Your! Databases!
tucankur: Have you checked for hardware errors? Reoccuring corruption can indicate a storage problem.
After a reindex the checkDB is clean? If you drop the xml index completely and recreate it does the problem come back?
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
July 8, 2008 at 10:50 am
Hi, as far as I know they still have the same problem but I've stopped working there now so can't take it any further.
Thanks anyway.
July 15, 2008 at 10:02 am
The situation was this;
We have a weekly, optimization job scheduled with the rebuild index task with Change free space per page percentage 10%.
We also had a nightly Check database integrity job scheduled.
We had dropped all the XML type indexes that were getting the consistency errors after our check integrity job and recreate them and run the integrity check again to see clean results.
However our check db results, after our weekly rebuild index job would show us the same errors once again.
Just yesterday, microsoft came back to me with the following so I have implemented a differnt type of reindexing job for our db optimization purposes that excludes the tables with XML type indexes. Please let me know if you need any more details from me on this issue and thanks for all your replies.
******************************
I was able to reproduce the issue on our end by rebuilding the indexes on this database. In my research I see that Rebuilding XML indexes is not valid and this has been documented in the Book Online:
Indexes on xml Data Type Columns http://msdn.microsoft.com/en-us/library/ms191497.aspx?s=1
Modifying an XML Index
The ALTER INDEX Transact-SQL DDL statement can be used to modify existing XML and non-XML indexes. However, not all the ALTER INDEX options are available to XML indexes. The following options are not valid when modifying XML indexes:
· The rebuild and set option IGNORE_DUP_KEY is not valid for XML indexes. The rebuild option ONLINE must be set to OFF for secondary XML indexes. The option DROP_EXISTING is not permitted in the ALTER INDEX statement. When rebuilding the index, connection options must be set as described in Setting Options (XML Index).
· The modifications of the primary key constraint in the user table are not automatically propagated to XML indexes. The user must drop the XML indexes first and then re-create them.
· If ALTER INDEX ALL is specified, it applies to both non-XML and XML indexes. Indexing options may be specified that are not valid for both types of indexes. In this case, the whole statement fails.
I believe to make sure we do not run into the issue again, we need to make sure that we do not include the tables that have XML indexes.
********************************
February 23, 2009 at 8:39 pm
Could you please let me know the script for the reindex job to exclude XML indexes?Thanks so much in advance.
August 7, 2009 at 6:26 am
Just to clarify, the response from Microsoft was to not maintain XML indexes?
December 31, 2009 at 12:00 pm
We are experiencing this problem and it seems to be a bug that is addressed in 2005-SP3-CU6. Here's a couple of links that I found. Working through them now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply