Sunday, February 19, 2012

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/info_corrupt_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:
> 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/info_corrupt_suspect_db.asp
> "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.|||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:
> > 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/info_corrupt_suspect_db.asp
> >
> > "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.
>|||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:
> > 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.
>|||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...
> >
> > Thanks Tibor - will check BOL tomorrow morning when I get into the office.
> >
> > John.
> >
> > Tibor Karaszi wrote:
> > > 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/info_corrupt_suspect_db.asp
> > >
> > > "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.
> >
> >
>
>|||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
> 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...
> > >
> > > Thanks Tibor - will check BOL tomorrow morning when I get into the
office.
> > >
> > > John.
> > >
> > > Tibor Karaszi wrote:
> > > > 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/info_corrupt_suspect_db.asp
> > > >
> > > > "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.
> > >
> > >
> >
> >
> >|||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...
> > 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...
> > > >
> > > > Thanks Tibor - will check BOL tomorrow morning when I get into the
> office.
> > > >
> > > > John.
> > > >
> > > > Tibor Karaszi wrote:
> > > > > 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/info_corrupt_suspect_db.asp
> > > > >
> > > > > "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.
> > > >
> > > >
> > >
> > >
> > >
>
>|||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
> 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...
> > > 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...
> > > > >
> > > > > Thanks Tibor - will check BOL tomorrow morning when I get into the
> > office.
> > > > >
> > > > > John.
> > > > >
> > > > > Tibor Karaszi wrote:
> > > > > > 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/info_corrupt_suspect_db.asp
> > > > > >
> > > > > > "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.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Paul:
That is the output from the database maintanence plan. That I think is
using DBCC CHECKDB? Let me also run it once manually tonight to see what I
get. By the way, the error is Error 8928.
And we are using SQL 2000, no service pack (SQL version 8.00.194)
Thanks,
Q
"Paul S Randal [MS]" wrote:
> 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
> > 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...
> > > > 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:
> > > >|||Paul:
Sorry, got the wrong version number for our SQL server. It is
Microsoft SQL Server 2000 - 8.00.818
Thanks,
Q
"Paul S Randal [MS]" wrote:
> 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
> > 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...
> > > > 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...
> > > > > >
> > > > > > Thanks Tibor - will check BOL tomorrow morning when I get into the
> > > office.
> > > > > >
> > > > > > John.
> > > > > >
> > > > > > Tibor Karaszi wrote:
> > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > >
> > > > > > > "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. Have you checked for evidence of hardware problems? (in the event
log and errorlog?)
--
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:63632DA0-E14C-4A0A-8532-7C9EB6680A14@.microsoft.com...
> Paul:
> Sorry, got the wrong version number for our SQL server. It is
> Microsoft SQL Server 2000 - 8.00.818
> Thanks,
> Q
> "Paul S Randal [MS]" wrote:
> > 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
> > > 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...
> > > > > 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...
> > > > > > >
> > > > > > > Thanks Tibor - will check BOL tomorrow morning when I get into
the
> > > > office.
> > > > > > >
> > > > > > > John.
> > > > > > >
> > > > > > > Tibor Karaszi wrote:
> > > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > > >
> > > > > > > > "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.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Paul:
Already looked at both event and errorlog, but no sign of hardware problem.
Thanks,
Q
"Paul S Randal [MS]" wrote:
> Thanks. Have you checked for evidence of hardware problems? (in the event
> log and errorlog?)
> --
> 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:63632DA0-E14C-4A0A-8532-7C9EB6680A14@.microsoft.com...
> > Paul:
> >
> > Sorry, got the wrong version number for our SQL server. It is
> > Microsoft SQL Server 2000 - 8.00.818
> >
> > Thanks,
> >
> > Q
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > 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
> > > > 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...
> > > > > > 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...
> > > > > > > >
> > > > > > > > Thanks Tibor - will check BOL tomorrow morning when I get into
> the
> > > > > office.
> > > > > > > >
> > > > > > > > John.
> > > > > > > >
> > > > > > > > Tibor Karaszi wrote:
> > > > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > > > >
> > > > > > > > > "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.
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||ok - seems like you're only option if you can't regenerate the database is
to run repair_allow_data_loss as your backup has the corruption in it too.
You could check with Product Support to see if there have ben any SQL Server
issues that could account for what you're seeing. As far as working out
exactly what data will be lost when the page is deleted, there's no
supported way of doing that, although googling DBCC PAGE may be useful...
Regards.
--
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:90B24295-03E5-47B2-A0D2-5DB839C1C1F9@.microsoft.com...
> Paul:
> Already looked at both event and errorlog, but no sign of hardware
problem.
> Thanks,
> Q
> "Paul S Randal [MS]" wrote:
> > Thanks. Have you checked for evidence of hardware problems? (in the
event
> > log and errorlog?)
> >
> > --
> > 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:63632DA0-E14C-4A0A-8532-7C9EB6680A14@.microsoft.com...
> > > Paul:
> > >
> > > Sorry, got the wrong version number for our SQL server. It is
> > > Microsoft SQL Server 2000 - 8.00.818
> > >
> > > Thanks,
> > >
> > > Q
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > 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
> > > > > 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...
> > > > > > > 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...
> > > > > > > > >
> > > > > > > > > Thanks Tibor - will check BOL tomorrow morning when I get
into
> > the
> > > > > > office.
> > > > > > > > >
> > > > > > > > > John.
> > > > > > > > >
> > > > > > > > > Tibor Karaszi wrote:
> > > > > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > > > > >
> > > > > > > > > > "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.
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Paul:
Do you think it will work if I can find the rows of data with problem,
delete them, and reload it from the raw data file?
Thanks,
Q
"Paul S Randal [MS]" wrote:
> ok - seems like you're only option if you can't regenerate the database is
> to run repair_allow_data_loss as your backup has the corruption in it too.
> You could check with Product Support to see if there have ben any SQL Server
> issues that could account for what you're seeing. As far as working out
> exactly what data will be lost when the page is deleted, there's no
> supported way of doing that, although googling DBCC PAGE may be useful...
> Regards.
> --
> 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:90B24295-03E5-47B2-A0D2-5DB839C1C1F9@.microsoft.com...
> > Paul:
> >
> > Already looked at both event and errorlog, but no sign of hardware
> problem.
> >
> > Thanks,
> >
> > Q
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > Thanks. Have you checked for evidence of hardware problems? (in the
> event
> > > log and errorlog?)
> > >
> > > --
> > > 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:63632DA0-E14C-4A0A-8532-7C9EB6680A14@.microsoft.com...
> > > > Paul:
> > > >
> > > > Sorry, got the wrong version number for our SQL server. It is
> > > > Microsoft SQL Server 2000 - 8.00.818
> > > >
> > > > Thanks,
> > > >
> > > > Q
> > > >
> > > > "Paul S Randal [MS]" wrote:
> > > >
> > > > > 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
> > > > > > 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...
> > > > > > > > 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...
> > > > > > > > > >
> > > > > > > > > > Thanks Tibor - will check BOL tomorrow morning when I get
> into
> > > the
> > > > > > > office.
> > > > > > > > > >
> > > > > > > > > > John.
> > > > > > > > > >
> > > > > > > > > > Tibor Karaszi wrote:
> > > > > > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > > > > > >
> > > > > > > > > > > "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.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||I don't know - depends how much of the record auditing code is read when the
record is instantiated to see whether it meets the predicates you need for
the delete operation. If it all gets run, you'll hit the same problem and
the delete will fail. Alternatively, find out what rows are bad, what rows
are on the page and let repair delete as much as it needs to and then reload
the deleted data from the file.
I don't know how successful either of these ideas will be.
Regards
--
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:04334E04-BF38-42EB-BABC-D85AE148466F@.microsoft.com...
> Paul:
> Do you think it will work if I can find the rows of data with problem,
> delete them, and reload it from the raw data file?
> Thanks,
> Q
> "Paul S Randal [MS]" wrote:
> > ok - seems like you're only option if you can't regenerate the database
is
> > to run repair_allow_data_loss as your backup has the corruption in it
too.
> > You could check with Product Support to see if there have ben any SQL
Server
> > issues that could account for what you're seeing. As far as working out
> > exactly what data will be lost when the page is deleted, there's no
> > supported way of doing that, although googling DBCC PAGE may be
useful...
> >
> > Regards.
> >
> > --
> > 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:90B24295-03E5-47B2-A0D2-5DB839C1C1F9@.microsoft.com...
> > > Paul:
> > >
> > > Already looked at both event and errorlog, but no sign of hardware
> > problem.
> > >
> > > Thanks,
> > >
> > > Q
> > >
> > > "Paul S Randal [MS]" wrote:
> > >
> > > > Thanks. Have you checked for evidence of hardware problems? (in the
> > event
> > > > log and errorlog?)
> > > >
> > > > --
> > > > 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:63632DA0-E14C-4A0A-8532-7C9EB6680A14@.microsoft.com...
> > > > > Paul:
> > > > >
> > > > > Sorry, got the wrong version number for our SQL server. It is
> > > > > Microsoft SQL Server 2000 - 8.00.818
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Q
> > > > >
> > > > > "Paul S Randal [MS]" wrote:
> > > > >
> > > > > > 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
> > > > > > > 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...
> > > > > > > > > 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...
> > > > > > > > > > >
> > > > > > > > > > > Thanks Tibor - will check BOL tomorrow morning when I
get
> > into
> > > > the
> > > > > > > > office.
> > > > > > > > > > >
> > > > > > > > > > > John.
> > > > > > > > > > >
> > > > > > > > > > > Tibor Karaszi wrote:
> > > > > > > > > > > > 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/info_corrupt_suspect_db.asp
> > > > > > > > > > > >
> > > > > > > > > > > > "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.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

No comments:

Post a Comment