November 28, 2007 at 2:12 pm
I have run DBCC CheckDB on a database and it reports this error:
There are 408 rows in 18 pages for object
Server: Msg 8928, Level 16, State 1, Line 1
Object ID [n], index id 2: Page (1:3311) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table Error: Object ID [n], index ID 2: Page (1:3311), row 213. Test (ColumnOffsets <= (nextRec-pRec)) failed. Values are 465 and 38.
CheckDB found 0 allocation errors and 2 consistency errors in database [db].
repair_allow_data_loss is the minimum repair level for the errors..
I have run DBCC CheckTable and CheckConstraints on the table and there are no errors reported and CheckAlloc on the database with no errors.
Shouldn't one of the lesser DBCC statements also find the error. I would prefer not to have to run the CheckDb to repair errors on 1 table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2007 at 1:19 pm
CHECKTABLE should find the same error on the table as CHECKDB, unless the page has since changed in the database (if there's any workload running), or you run into one of the (very few) false-failure cases in SQL 2000's check algorithm (which is why I changed the algorithm when I re-wrote CHEKCDB for SQL 2005).
Is the corruption still reported by CHECKDB?
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
December 2, 2007 at 1:21 pm
Come to think of it - that error isn't one that will ever be falsely reported. If the error is still reported by CHECKDB then it should be seen by CHECKTABLE too. Let me know if it is and we can poke around with DBCC PAGE to see what's going on. In the meantime, hopefully you've restored from you backup onto another system - errors of this kind are usually caused by something in the IO subsystem corrupting bits/bytes.
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
December 3, 2007 at 7:19 am
Paul,
Thanks for the replies. When I ran CheckDB and CheckTable there were no other processes running on the SQL server. I was working on the SQL Server because it had had drive failure and we had restored to a new drive using an image. I should mention that this is SQL Server 200 Personal Edition SP3 running on Windows 2000 Professional. Could it be that CheckDB is reporting a phantom error?
I ran CheckTable with the Table name reported by CheckDB just prior to the error. Is that correct?
If I had included the index id would I be more likely to duplicate the error reported by CheckDB?
I had no acceptable backups available to me because there had been a problem that caused msdb to be suspect, so the backup jobs had not run since mid-August (I was not aware of this SQL Server until brought on to fix this problem). Would restoring from a backup taken now help the issue?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2007 at 11:27 am
Ah - so you ran CHECKTABLE before CHECKDB. It's entirely possible that the corruption occured between the time you ran CHECKTABLE and the time you ran CHECKDB. Does CHECKTABLE now show the corruption?
Specifying the index ID will actually narrow down the checks the CHECKTABLE does to just the index specified (plus the base table), but won't change the algorithms used and so won't affect the corruptions reported on that index.
No, restoring from a backup taken now won't change anything as the backup will contain the corruption. As this is a non-clustered index, you haven't lost any actual data and so rebuilding the non-clustered index should repair the problem with no data-loss. You should also do some root-cause analysis to figure out why the problem occured in the first place - if its an IO subsystem issue then the odds are it will happen again in the future.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply