Friday, February 24, 2012

DBCC CheckTable shows consistency errors

We recently migrated our SQL Server 2000 cluster to a new set of
hardware. We are now having sporadic table consistency errors.
Then we started having trouble with some of our tables. Yesterday, one
of our tables had a consistency error, which we dropped (it was
non-essential). Then today a different table (which had worked fine
yesterday) came up with consistency errors.
When I ran DBCC CheckTable on the table that has a problem today, this
is the result:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2039678314, index ID 0: Page (1:1144605) could not be
processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2039678314, index ID 0: Page (1:1694520) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 2039678314, index ID 0, page (1:1694520), row
7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 1475
and 96.
DBCC results for 'ATN_Account_Charges'.
There are 4834481 rows in 78193 pages for object
'ATN_Account_Charges'.
CHECKTABLE found 0 allocation errors and 3 consistency errors in table
'ATN_Account_Charges' (object ID 2039678314).
repair_allow_data_loss is the minimum repair level for the errors
found by DBCC CHECKTABLE (CAT.dbo.ATN_Account_Charges ).
Each time someone tries to access this table, SQL Server generates a
.mdmp file in the LOGs folder.
Any ideas?
Thanks,
Lee"Lee" <lfalin@.ccitelecom.com> wrote in message
news:46781cf7.0311210840.15088d6d@.posting.google.com...
> We recently migrated our SQL Server 2000 cluster to a new set of
> hardware. We are now having sporadic table consistency errors.
> Then we started having trouble with some of our tables. Yesterday, one
> of our tables had a consistency error, which we dropped (it was
> non-essential). Then today a different table (which had worked fine
> yesterday) came up with consistency errors.
> When I ran DBCC CheckTable on the table that has a problem today, this
> is the result:
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2039678314, index ID 0: Page (1:1144605) could not be
> processed. See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2039678314, index ID 0: Page (1:1694520) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 2039678314, index ID 0, page (1:1694520), row
> 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 1475
> and 96.
> DBCC results for 'ATN_Account_Charges'.
> There are 4834481 rows in 78193 pages for object
> 'ATN_Account_Charges'.
> CHECKTABLE found 0 allocation errors and 3 consistency errors in table
> 'ATN_Account_Charges' (object ID 2039678314).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (CAT.dbo.ATN_Account_Charges ).
> Each time someone tries to access this table, SQL Server generates a
> .mdmp file in the LOGs folder.
> Any ideas?
It appears you have some internal corruption, you could start by running
DBCC CHECKTABLE ( 'tablename' , REPAIR_REBUILD )
on your effected tables. This will not cause data loss -- see BOL for
additional options and syntax. The output you pasted does show
"repair_allow_data_loss is the minimum repair level for the errors", which
is another option of DBCC, and may cause data loss. If you can track the
error down to an index, you may be able to drop and rebuild the indexes for
the effected tables...
Steve|||You have a corrupt record on the page. The record audit code is generating
an exception (and hence the mdmp you're seeing) whenever it encounters this
record during a query.
It sounds like the problems started happening when you moved to the new
hardware, and given the recurrence of the problem, it seems like you've got
bad hardware somewhere. You should look through the NT event logs and the
SQL Server error log for evidence of hardware problems. You should also run
all relevant hardware diagnostics on your new hardware.
If you still cannot isolate the cause, PSS should be able to help you.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <lfalin@.ccitelecom.com> wrote in message
news:46781cf7.0311210840.15088d6d@.posting.google.com...
> We recently migrated our SQL Server 2000 cluster to a new set of
> hardware. We are now having sporadic table consistency errors.
> Then we started having trouble with some of our tables. Yesterday, one
> of our tables had a consistency error, which we dropped (it was
> non-essential). Then today a different table (which had worked fine
> yesterday) came up with consistency errors.
> When I ran DBCC CheckTable on the table that has a problem today, this
> is the result:
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2039678314, index ID 0: Page (1:1144605) could not be
> processed. See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2039678314, index ID 0: Page (1:1694520) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 2039678314, index ID 0, page (1:1694520), row
> 7. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 1475
> and 96.
> DBCC results for 'ATN_Account_Charges'.
> There are 4834481 rows in 78193 pages for object
> 'ATN_Account_Charges'.
> CHECKTABLE found 0 allocation errors and 3 consistency errors in table
> 'ATN_Account_Charges' (object ID 2039678314).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (CAT.dbo.ATN_Account_Charges ).
> Each time someone tries to access this table, SQL Server generates a
> .mdmp file in the LOGs folder.
> Any ideas?
> Thanks,
> Lee

No comments:

Post a Comment