DBCC CHECKDB results - repairable?

Hello,
I ran DBCC CHECKDB on a database with some performance issues today and
received several errors. I restored from a backup (from last week, the
earliest one we had) as the database has not gone into production use yet.
The database however does contain a considerable amount of data as we had
done a data conversion from the customer's previous solution.
The errors on the backup are as follows (I have snipped out the information
messages)
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2015398299, index ID 0: Page (1:402237) could not be processed.
See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 2015398299, index ID 0, page (1:402237), row 39. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 37 and 35.
There are 481044 rows in 2208 pages for object 'ORDERS'.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ORDERS'
(object ID 2015398299).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'CUST11838'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (CUST11838 ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Are these errors likely to be permanently repairable by running CHECKDB with
repair_allow_data_loss, or should we really be starting our data migration
processes again (delaying our customer's 'go live' by possibly 3-4 days)?
Many thanks,
John.
Below is a good place to start. And remember that the updated books online has *detailed and
specific* recommendations for all corruption types of errors.
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:efrPEYkpEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I ran DBCC CHECKDB on a database with some performance issues today and received several errors.
> I restored from a backup (from last week, the earliest one we had) as the database has not gone
> into production use yet. The database however does contain a considerable amount of data as we had
> done a data conversion from the customer's previous solution.
> The errors on the backup are as follows (I have snipped out the information messages)
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 2015398299, index ID 0: Page (1:402237) could not be processed. See other errors for
> details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 2015398299, index ID 0, page (1:402237), row 39. Test (ColumnOffsets <=
> (nextRec - pRec)) failed. Values are 37 and 35.
> There are 481044 rows in 2208 pages for object 'ORDERS'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table 'ORDERS' (object ID
> 2015398299).
> CHECKDB found 0 allocation errors and 2 consistency errors in database 'CUST11838'.
> repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
> (CUST11838 ).
> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>
> Are these errors likely to be permanently repairable by running CHECKDB with
> repair_allow_data_loss, or should we really be starting our data migration processes again
> (delaying our customer's 'go live' by possibly 3-4 days)?
> Many thanks,
> John.
>
|||Thanks Tibor - will check BOL tomorrow morning when I get into the office.
John.
Tibor Karaszi wrote:[vbcol=seagreen]
> Below is a good place to start. And remember that the updated books
> online has *detailed and specific* recommendations for all corruption
> types of errors.
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> "John McLusky" <jmclusky@.community.nospam> wrote in message
> news:efrPEYkpEHA.3520@.TK2MSFTNGP11.phx.gbl...
|||Yes, the error will be repaired, but the repair will delete the page, losing
the data on it. Given that this is a page in your ORDERS table for a
customer, I suggest you start again for that table rather than losing that
data.
If you have any question after reading the new BOL (which I wrote), please
let me know.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:#PgE8mkpEHA.3300@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor - will check BOL tomorrow morning when I get into the office.
> John.
> Tibor Karaszi wrote:
>
|||Paul S Randal [MS] wrote:
> Yes, the error will be repaired, but the repair will delete the page,
> losing the data on it. Given that this is a page in your ORDERS table
> for a customer, I suggest you start again for that table rather than
> losing that data.
I suspected that this might be the case, but I needed to be sure.
We can in fact redo the data migration for this table only rather than for
the whole database, saving us a couple of days of processing time. As long
as the rest of the database will be in working order after the
repair_allow_data_loss I'm happy with the result.

> If you have any question after reading the new BOL (which I wrote),
> please let me know.
Will do. Thanks for the quick response!
John.
|||If you're going to recreate the table, I recommend simply deleting the table
rather than going to the trouble of putting the db into single-user mode and
re-running CHECKDB to repair the bad page. You should also investigate how
the corruption occured in the first place and also check to ensure it
doesn't return.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:u7#sTsmpEHA.3424@.TK2MSFTNGP11.phx.gbl...
> Paul S Randal [MS] wrote:
> I suspected that this might be the case, but I needed to be sure.
> We can in fact redo the data migration for this table only rather than for
> the whole database, saving us a couple of days of processing time. As
long
> as the rest of the database will be in working order after the
> repair_allow_data_loss I'm happy with the result.
>
> Will do. Thanks for the quick response!
> John.
>
|||Would it be possible to find out what actually data will be lost? I ran a
integrity check last weekend and encountered a consistency error in one of
the tables. One option is to run DBCC to repair it, but we may have data
loss. But, if I know exactly what will be lost, then I can load those data
back to the database.
Thanks,
"Paul S Randal [MS]" wrote:

> Yes, the error will be repaired, but the repair will delete the page, losing
> the data on it. Given that this is a page in your ORDERS table for a
> customer, I suggest you start again for that table rather than losing that
> data.
> If you have any question after reading the new BOL (which I wrote), please
> let me know.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "John McLusky" <jmclusky@.community.nospam> wrote in message
> news:#PgE8mkpEHA.3300@.TK2MSFTNGP12.phx.gbl...
>
>
|||Its not possible to tell exactly without looking at the errors and the
database and knowing exactly how repair will deal with the errors
internally. What was the error you encountered?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E5CB428E-64B5-49CA-BC0D-90A82B47B897@.microsoft.com...
> Would it be possible to find out what actually data will be lost? I ran
a
> integrity check last weekend and encountered a consistency error in one of
> the tables. One option is to run DBCC to repair it, but we may have data
> loss. But, if I know exactly what will be lost, then I can load those
data[vbcol=seagreen]
> back to the database.
> Thanks,
> "Paul S Randal [MS]" wrote:
losing[vbcol=seagreen]
that[vbcol=seagreen]
please[vbcol=seagreen]
rights.[vbcol=seagreen]
office.[vbcol=seagreen]
corruption[vbcol=seagreen]
considerable[vbcol=seagreen]
permanently[vbcol=seagreen]
starting[vbcol=seagreen]
|||Hello Paul:
Here is the error:
[4] Database Order_Data: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 1077578877, index ID 0: Page (5:183991)
could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1077578877, index ID 0, page (5:183991), row 0. Test (ColumnOffsets <=
(nextRec - pRec)) failed. Values are 36 and 13.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'tblData2' (object ID 1077578877).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'Order_Data'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (Order_Data ).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 1077578877, index
ID 0: Page (5:183991) could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1077578877, index ID 0, page (5:183991), row 0. Test (ColumnOffsets <=
(nextRec - pRec)) failed. Values are 36 and 13.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'tblData2' (object ID 1077578877).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'Order_Data'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (Order_Data ).
Thanks.
Q
"Paul S Randal [MS]" wrote:

> Its not possible to tell exactly without looking at the errors and the
> database and knowing exactly how repair will deal with the errors
> internally. What was the error you encountered?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E5CB428E-64B5-49CA-BC0D-90A82B47B897@.microsoft.com...
> a
> data
> losing
> that
> please
> rights.
> office.
> corruption
> considerable
> permanently
> starting
>
>
|||Looks like you've got different errors from the ones you reported earlier.
Can you run a manual DBCC CHECKDB on that database and post the results (so
we can see the actual error numbers)?
For the errors you reported (a heap record contains column offsets that are
beyond the end of the record), repair will either deallocate the record or
the whole page. You'd need to look at the page contents to work out what's
actually going to be lost.
Which SQL Server version and SP level is this?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Q" <Q@.discussions.microsoft.com> wrote in message
news:284CBC96-CBF6-47FD-A7F9-98BF9EDCB78C@.microsoft.com...
> Hello Paul:
> Here is the error:
> [4] Database Order_Data: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Object ID 1077578877, index ID 0: Page
(5:183991)
> could not be processed. See other errors for details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1077578877, index ID 0, page (5:183991), row 0. Test (ColumnOffsets <=
> (nextRec - pRec)) failed. Values are 36 and 13.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'tblData2' (object ID
1077578877).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'Order_Data'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is
the
> minimum repair level for the errors found by DBCC CHECKDB (Order_Data ).
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 1077578877, index
> ID 0: Page (5:183991) could not be processed. See other errors for
details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1077578877, index ID 0, page (5:183991), row 0. Test (ColumnOffsets <=
> (nextRec - pRec)) failed. Values are 36 and 13.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'tblData2' (object ID
1077578877).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'Order_Data'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is
the[vbcol=seagreen]
> minimum repair level for the errors found by DBCC CHECKDB (Order_Data ).
> Thanks.
> Q
> "Paul S Randal [MS]" wrote:
rights.[vbcol=seagreen]
ran[vbcol=seagreen]
one of[vbcol=seagreen]
data[vbcol=seagreen]
page,[vbcol=seagreen]
losing[vbcol=seagreen]
books[vbcol=seagreen]
today[vbcol=seagreen]
last[vbcol=seagreen]
into[vbcol=seagreen]
customer's[vbcol=seagreen]
have[vbcol=seagreen]
1[vbcol=seagreen]
row[vbcol=seagreen]
37[vbcol=seagreen]
'ORDERS'.[vbcol=seagreen]
table[vbcol=seagreen]
repair[vbcol=seagreen]
contact[vbcol=seagreen]