September 9, 2009 at 8:56 am
I have encountered a recurring index corruption problem with just one particualr table in a database. This same database structure lives on multiple servers, different sql server versions, different hardware, ... etc. In each case it's always this same table. For this reason I do not believe that this is a hardware issue. My solution has been to select data out to a temp table, drop and re-create the corrupted table, then insert the records back in (from the corrupted temp table). this fixes the corruption every time with no loss of data. I'm investigating how this data is entering the database (data uploaded from handheld devise via xml post). My theory is that there is a problem in transmission but I don't have proof.
Below is the result from dbcc checkdb showing the corruption. If any one has any ideas please or has had similar issues in the past, please let me know. Thanks.
Msg 8929, Level 16, State 1, Line 1
Object ID 1749541962, index ID 0, partition ID 72057594460504064, alloc unit ID 72057594477477888 (type In-row data): Errors found in off-row data with ID 91042152448 owned by data record identified by RID = (1:54952:1)
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1749541962, index ID 0, partition ID 72057594460504064, alloc unit ID 72057594477477888 (type In-row data), page ID (1:54952), row ID 1. Column 'SigPts' was created NOT NULL, but is NULL in the row.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1749541962, index ID 0, partition ID 72057594460504064, alloc unit ID 72057594477543424 (type LOB data). The off-row data node at page (1:55118), slot 13, text ID 91042152448 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1749541962, index ID 0, partition ID 72057594460504064, alloc unit ID 72057594477543424 (type LOB data). The off-row data node at page (1:55248), slot 1, text ID 91042217984 is not referenced.
September 9, 2009 at 9:01 am
Generally corruption is an IO subsystem problem. If you're having repeated corruption, I'd take a long, hard look at the drives/SAN that the server uses. Have a look in the Windows event log, see if there are any IO/drive-related errors. If it's a SAN, check the drivers on the HBA cards, firmware on the switches and controllers, make sure it's up to date.
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
September 9, 2009 at 9:20 am
Thanks for the quick reply. I agree that usually hardware would be the first place to look. In my case Windows event logs don't show any issues. I do know that my firmware for the SAN needs to be updated. However, the reason why I dismissed hardware as the cause is because this same database is installed on hundreds of other servers and this same table gets index corruption on other servers as well.
If I bcp the data out, drop and re-create the table, then bcp the data back in - that fixes the corruption.
September 9, 2009 at 9:52 am
What's the version of SQL 2005 that you're using?
I'm going to consult with an expert on corruption on this.
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
September 9, 2009 at 9:55 am
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thank you.
September 9, 2009 at 12:41 pm
This looks like a bug - and it seems like you have a repro too. You should call Product Support to help figure out the problem. I haven't seen any bugs that would cause this in SP2.
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
September 25, 2009 at 12:52 pm
Is this resolved?
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy