I run into a small issue at the end last week, a CHECKDB job was reporting corruption on one of my development databases:

Msg 8964, Level 16, State 1, Line 1

Table error: Object ID 965578478, index ID 1, partition ID 72057594042515456, alloc unit ID 72057594052083712 (type LOB data). The off-row data node at page (1:569), slot 0, text ID 1881079808 is not referenced.

After an initial hiccup and slight panic I started with the SQL Server error log which had entries similar to the above

I won’t go into all the details in this post but after troubleshooting for a short while and reading a few posts and articles including Gail Shaw’s article on SQL Server Central (http://www.sqlservercentral.com/articles/65804/) and Paul Randal’s blog (http://www.sqlskills.com/BLOGS/PAUL/). I discovered it was known issue.

Earlier in the week I had created an XML index on one of the tables and since its creation an index rebuild job had run on the newly created XML index.

I wanted to note down this URL for future reference:

This is the knowledge base article detailing the issue and possible workarounds.

http://support.microsoft.com/kb/974985

There is a cumulative update (CU) that fixes this issue and it is fixed in SQL Server 2008. One work around suggested and the one I’m currently using was to drop and recreate the index instead of using ALTER INDEX with REBUILD statement.