DBCC CHECKDB Questions

1. In one of our live, multi-user, production databases, DBCC CHECKDB has
reported errors that have disappeared in subsequent runs of DBCC CHECKDB,
this without any repair options. What is happening? Is SqlServer actually
repairing them anyway or are they getting swept under the rug in some way?
This is an example of an error that got that subsequently went away:
[2] Database VMed1: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page (1:21349)
could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
2050874423, index ID 2, page (1:21349), row 339. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 13 and 3.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'NodeRelation' (object ID
2050874423).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'VMed1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (VMed1 ).
2. Does anyone have a script containing an example of how to run DBCC
CHECKDB and (1) conditionally set the database to Single-User mode if there
are errors and (2) repair the errors and (3) conditionally escalate to ever
more aggressive repair modes? Or is a script even a good idea, as the next
question implies?
3. Is any form of CHECKDB repairing something that should be done
unattended? Seems like unattended would conflict with the requirement for
single-user mode.
4. If data must be lost during the repair, how is one to know whether or not
the database is in such bad shape that it should be restored from a backup?
Or should always restore from a backup if data is lost during repair?
Thanks,
Randy Neall> 3. Is any form of CHECKDB repairing something that should be done
> unattended?
No, I do not recommend it. If you want more support to this standpoint, search the archives, it is
among others supported by MS dev team members.
> 4. If data must be lost during the repair, how is one to know whether or not
> the database is in such bad shape that it should be restored from a backup?
You search in Books Online for the specific error number. Each error number is documented with
specific recommendations. A problem with maint wiz is that it only reports error number for first
error. But you can execute from QA (if small database) or have a TSQL jobstep and an Agent output
file. Also, here you will find that most recommend restore from backup instead of repair. In many
cases, if you have log backups in place, you can end up with zero data loss.
For your specific problem, I'd Google, search KB and also the newsgroups archives to see if there
are other experiences with that particular error (or sequence or errors) that has been vanishing.
One problem is that you only have error number from first error, but you should be able to find out
error number for the others by some BOL searching (or searching though sysmessages).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:%23IAH1wUkFHA.3288@.TK2MSFTNGP10.phx.gbl...
> 1. In one of our live, multi-user, production databases, DBCC CHECKDB has
> reported errors that have disappeared in subsequent runs of DBCC CHECKDB,
> this without any repair options. What is happening? Is SqlServer actually
> repairing them anyway or are they getting swept under the rug in some way?
> This is an example of an error that got that subsequently went away:
> [2] Database VMed1: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page (1:21349)
> could not be processed. See other errors for details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 2050874423, index ID 2, page (1:21349), row 339. Test (ColumnOffsets <=> (nextRec - pRec)) failed. Values are 13 and 3.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'NodeRelation' (object ID
> 2050874423).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'VMed1'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
> minimum repair level for the errors found by DBCC CHECKDB (VMed1 ).
> 2. Does anyone have a script containing an example of how to run DBCC
> CHECKDB and (1) conditionally set the database to Single-User mode if there
> are errors and (2) repair the errors and (3) conditionally escalate to ever
> more aggressive repair modes? Or is a script even a good idea, as the next
> question implies?
> 3. Is any form of CHECKDB repairing something that should be done
> unattended? Seems like unattended would conflict with the requirement for
> single-user mode.
> 4. If data must be lost during the repair, how is one to know whether or not
> the database is in such bad shape that it should be restored from a backup?
> Or should always restore from a backup if data is lost during repair?
>
> Thanks,
> Randy Neall
>|||You also might want to check out 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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%23MvN$kfkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> 3. Is any form of CHECKDB repairing something that should be done
>> unattended?
> No, I do not recommend it. If you want more support to this standpoint, search the archives, it is
> among others supported by MS dev team members.
>
>> 4. If data must be lost during the repair, how is one to know whether or not
>> the database is in such bad shape that it should be restored from a backup?
> You search in Books Online for the specific error number. Each error number is documented with
> specific recommendations. A problem with maint wiz is that it only reports error number for first
> error. But you can execute from QA (if small database) or have a TSQL jobstep and an Agent output
> file. Also, here you will find that most recommend restore from backup instead of repair. In many
> cases, if you have log backups in place, you can end up with zero data loss.
> For your specific problem, I'd Google, search KB and also the newsgroups archives to see if there
> are other experiences with that particular error (or sequence or errors) that has been vanishing.
> One problem is that you only have error number from first error, but you should be able to find
> out error number for the others by some BOL searching (or searching though sysmessages).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:%23IAH1wUkFHA.3288@.TK2MSFTNGP10.phx.gbl...
>> 1. In one of our live, multi-user, production databases, DBCC CHECKDB has
>> reported errors that have disappeared in subsequent runs of DBCC CHECKDB,
>> this without any repair options. What is happening? Is SqlServer actually
>> repairing them anyway or are they getting swept under the rug in some way?
>> This is an example of an error that got that subsequently went away:
>> [2] Database VMed1: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL
>> Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page (1:21349)
>> could not be processed. See other errors for details.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
>> 2050874423, index ID 2, page (1:21349), row 339. Test (ColumnOffsets <=>> (nextRec - pRec)) failed. Values are 13 and 3.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
>> errors and 2 consistency errors in table 'NodeRelation' (object ID
>> 2050874423).
>> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
>> errors and 2 consistency errors in database 'VMed1'.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
>> minimum repair level for the errors found by DBCC CHECKDB (VMed1 ).
>> 2. Does anyone have a script containing an example of how to run DBCC
>> CHECKDB and (1) conditionally set the database to Single-User mode if there
>> are errors and (2) repair the errors and (3) conditionally escalate to ever
>> more aggressive repair modes? Or is a script even a good idea, as the next
>> question implies?
>> 3. Is any form of CHECKDB repairing something that should be done
>> unattended? Seems like unattended would conflict with the requirement for
>> single-user mode.
>> 4. If data must be lost during the repair, how is one to know whether or not
>> the database is in such bad shape that it should be restored from a backup?
>> Or should always restore from a backup if data is lost during repair?
>>
>> Thanks,
>> Randy Neall
>>
>|||Thank you Tibor, and thank you for your own web reference at karaszi.com.
There I read that and one thing jumped out at me: anti-virus software, which
we have (Avast in our case), and it does scan our SqlDatabase on a
continuous basis (I think). If this is a mistake, or if there is some better
way to involve anti-virus software, we need to know. It seems to me that
anti-virus software should scan the database only when the database is shut
down, so this might be a huge mistake we are making.
You also suggested looking at even logs to find out the cause of the
problem. What event logs did you have in mind? We do not see any relevant
events in the event logs we looked at.
Randy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23XG2rmfkFHA.3164@.TK2MSFTNGP15.phx.gbl...
> You also might want to check out
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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
> news:%23MvN$kfkFHA.3316@.TK2MSFTNGP14.phx.gbl...
> >> 3. Is any form of CHECKDB repairing something that should be done
> >> unattended?
> >
> > No, I do not recommend it. If you want more support to this standpoint,
search the archives, it is
> > among others supported by MS dev team members.
> >
> >
> >> 4. If data must be lost during the repair, how is one to know whether
or not
> >> the database is in such bad shape that it should be restored from a
backup?
> >
> > You search in Books Online for the specific error number. Each error
number is documented with
> > specific recommendations. A problem with maint wiz is that it only
reports error number for first
> > error. But you can execute from QA (if small database) or have a TSQL
jobstep and an Agent output
> > file. Also, here you will find that most recommend restore from backup
instead of repair. In many
> > cases, if you have log backups in place, you can end up with zero data
loss.
> >
> > For your specific problem, I'd Google, search KB and also the newsgroups
archives to see if there
> > are other experiences with that particular error (or sequence or errors)
that has been vanishing.
> > One problem is that you only have error number from first error, but you
should be able to find
> > out error number for the others by some BOL searching (or searching
though sysmessages).
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> > news:%23IAH1wUkFHA.3288@.TK2MSFTNGP10.phx.gbl...
> >> 1. In one of our live, multi-user, production databases, DBCC CHECKDB
has
> >> reported errors that have disappeared in subsequent runs of DBCC
CHECKDB,
> >> this without any repair options. What is happening? Is SqlServer
actually
> >> repairing them anyway or are they getting swept under the rug in some
way?
> >>
> >> This is an example of an error that got that subsequently went away:
> >>
> >> [2] Database VMed1: Check Data and Index Linkage...
> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
SQL
> >> Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page
(1:21349)
> >> could not be processed. See other errors for details.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> >> 2050874423, index ID 2, page (1:21349), row 339. Test (ColumnOffsets <=> >> (nextRec - pRec)) failed. Values are 13 and 3.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
allocation
> >> errors and 2 consistency errors in table 'NodeRelation' (object ID
> >> 2050874423).
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
allocation
> >> errors and 2 consistency errors in database 'VMed1'.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
is the
> >> minimum repair level for the errors found by DBCC CHECKDB (VMed1 ).
> >>
> >> 2. Does anyone have a script containing an example of how to run DBCC
> >> CHECKDB and (1) conditionally set the database to Single-User mode if
there
> >> are errors and (2) repair the errors and (3) conditionally escalate to
ever
> >> more aggressive repair modes? Or is a script even a good idea, as the
next
> >> question implies?
> >>
> >> 3. Is any form of CHECKDB repairing something that should be done
> >> unattended? Seems like unattended would conflict with the requirement
for
> >> single-user mode.
> >>
> >> 4. If data must be lost during the repair, how is one to know whether
or not
> >> the database is in such bad shape that it should be restored from a
backup?
> >> Or should always restore from a backup if data is lost during repair?
> >>
> >>
> >> Thanks,
> >>
> >> Randy Neall
> >>
> >>
> >
>|||the anti-virus example is a special case. You start SQL Server, but the AV sw is scanning the file
meaning that SQL Server cannot open the file for excusive user. The database is marked as suspect.
I.e., this should not cause any corruption.
I'm referring to either the Windows eventlog or the SQL Server errorlog file. You need to track down
then the corruption occur. At that time, SQL server should have written error messages why this
happened.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:uw9jbcskFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Thank you Tibor, and thank you for your own web reference at karaszi.com.
> There I read that and one thing jumped out at me: anti-virus software, which
> we have (Avast in our case), and it does scan our SqlDatabase on a
> continuous basis (I think). If this is a mistake, or if there is some better
> way to involve anti-virus software, we need to know. It seems to me that
> anti-virus software should scan the database only when the database is shut
> down, so this might be a huge mistake we are making.
> You also suggested looking at even logs to find out the cause of the
> problem. What event logs did you have in mind? We do not see any relevant
> events in the event logs we looked at.
> Randy
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23XG2rmfkFHA.3164@.TK2MSFTNGP15.phx.gbl...
>> You also might want to check out
> 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/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message
>> news:%23MvN$kfkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>> >> 3. Is any form of CHECKDB repairing something that should be done
>> >> unattended?
>> >
>> > No, I do not recommend it. If you want more support to this standpoint,
> search the archives, it is
>> > among others supported by MS dev team members.
>> >
>> >
>> >> 4. If data must be lost during the repair, how is one to know whether
> or not
>> >> the database is in such bad shape that it should be restored from a
> backup?
>> >
>> > You search in Books Online for the specific error number. Each error
> number is documented with
>> > specific recommendations. A problem with maint wiz is that it only
> reports error number for first
>> > error. But you can execute from QA (if small database) or have a TSQL
> jobstep and an Agent output
>> > file. Also, here you will find that most recommend restore from backup
> instead of repair. In many
>> > cases, if you have log backups in place, you can end up with zero data
> loss.
>> >
>> > For your specific problem, I'd Google, search KB and also the newsgroups
> archives to see if there
>> > are other experiences with that particular error (or sequence or errors)
> that has been vanishing.
>> > One problem is that you only have error number from first error, but you
> should be able to find
>> > out error number for the others by some BOL searching (or searching
> though sysmessages).
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
>> > news:%23IAH1wUkFHA.3288@.TK2MSFTNGP10.phx.gbl...
>> >> 1. In one of our live, multi-user, production databases, DBCC CHECKDB
> has
>> >> reported errors that have disappeared in subsequent runs of DBCC
> CHECKDB,
>> >> this without any repair options. What is happening? Is SqlServer
> actually
>> >> repairing them anyway or are they getting swept under the rug in some
> way?
>> >>
>> >> This is an example of an error that got that subsequently went away:
>> >>
>> >> [2] Database VMed1: Check Data and Index Linkage...
>> >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC
> SQL
>> >> Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page
> (1:21349)
>> >> could not be processed. See other errors for details.
>> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
>> >> 2050874423, index ID 2, page (1:21349), row 339. Test (ColumnOffsets <=>> >> (nextRec - pRec)) failed. Values are 13 and 3.
>> >> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation
>> >> errors and 2 consistency errors in table 'NodeRelation' (object ID
>> >> 2050874423).
>> >> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation
>> >> errors and 2 consistency errors in database 'VMed1'.
>> >> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss
> is the
>> >> minimum repair level for the errors found by DBCC CHECKDB (VMed1 ).
>> >>
>> >> 2. Does anyone have a script containing an example of how to run DBCC
>> >> CHECKDB and (1) conditionally set the database to Single-User mode if
> there
>> >> are errors and (2) repair the errors and (3) conditionally escalate to
> ever
>> >> more aggressive repair modes? Or is a script even a good idea, as the
> next
>> >> question implies?
>> >>
>> >> 3. Is any form of CHECKDB repairing something that should be done
>> >> unattended? Seems like unattended would conflict with the requirement
> for
>> >> single-user mode.
>> >>
>> >> 4. If data must be lost during the repair, how is one to know whether
> or not
>> >> the database is in such bad shape that it should be restored from a
> backup?
>> >> Or should always restore from a backup if data is lost during repair?
>> >>
>> >>
>> >> Thanks,
>> >>
>> >> Randy Neall
>> >>
>> >>
>> >
>