Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQL_Hacker
Starting Member
2 Posts |
 Posted - 2009-01-27 : 15:56:18
|
| Hi,I'm running SQL Server 2005, SP2 on a virtual (VMWare) Windows Server 2003 machine, with a SAN supplying the hard drive partitions. There have been no reported hardware issues with this or any VM on this particular VMWare config. Also, no errors in the SQL Log regarding I/O issues.I've read several posts here and on other sites about what to do when receiving DBCC errors, but most of them have a different error message than I'm getting on my server. I've run DBCC CHECKDB (dbname), WITH ALL_ERRORMSGS on every database, and only one is having any issues at all. I believe the error is indicating there's a problem with an index, but I'm not 100% sure. Here's the text of the output after running the above command:DBCC results for 'sys.xml_index_nodes_8387099_32009'.Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1676585061, index ID 1, partition ID 72057594786021376, alloc unit ID 72057594877378560 (type LOB data). The off-row data node at page (3:3802003), slot 0, text ID 4259840 is not referenced.(There are literally 1000 of the above messages in the actual log file, but the Object ID does not change throughout.)The query has exceeded the maximum number of error messages. Only the first 1000 messages will be displayed.There are 732626 rows in 24056 pages for object "sys.xml_index_nodes_8387099_32009".CHECKDB found 0 allocation errors and 1400 consistency errors in table 'sys.xml_index_nodes_8387099_32009' (object ID 1676585061).The other troubling part is this issue has been happening since mid-December, and nobody investigated to see what was causing the automated CHECKDB job to fail. We only keep backups on the server for 3 weeks, and this has been happening at least 6 weeks. So...the chances of recovering the database to a point in time prior to this issue are grim indeed. Hypothetically, if a repair was run on this database, and the only thing wrong with it is this index, then there shouldn't be too many issues, and very little chance for actual data corruption...right? (please say "yes"...)Thanks for any help you can provide. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-01-27 : 18:51:34
|
| One of the XML indexes that's been created on a user table has a bunch of corruption (most likely caused by IO subsystem issues, but maybe a bug - I'm not aware of any KB articles mentioning this error).You should be able to find which table the XML index is for (I think the DMV is called sys.xml_indexes) - the table referenced in the errors is an 'internal table' that actually stores the XML index - in much the same way as an indexed view is stored. You should be able to match its object ID to the output from sys.xml_indexes - then find the object ID of the real table and drop/create the broken XML index to fix the problem.Let me know how you get on.Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)SQL Server MVP, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database course |
 |
|
|
SQL_Hacker
Starting Member
2 Posts |
Posted - 2009-01-28 : 14:13:18
|
| Paul,Thank you for the information. I was able to find the affected table (finally!!) and am going to drop/re-add the index as you recommended. I have to wait for "off-hours" to implement, but I'll post a final response once the index is dropped/re-added and I run CHECKDB on that database again. |
|
|
|
|
|
|
|
|
Index issues after CheckDB run