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]&#
91;ODBC SQL
Server Driver][SQL Server]Object ID 2050874423, index ID 2: Page (1:2134
9)
could not be processed. See other errors for details.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Obje
ct 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 a
llocation
errors and 2 consistency errors in table 'NodeRelation' (object ID
2050874423).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 a
llocation
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, sear
ch 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 n
ot
> the database is in such bad shape that it should be restored from a backup?[/vbcol
]
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 e
rror number for first
error. But you can execute from QA (if small database) or have a TSQL jobste
p and an Agent output
file. Also, here you will find that most recommend restore from backup inste
ad 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 arc
hives to see if there
are other experiences with that particular error (or sequence or errors) tha
t has been vanishing.
One problem is that you only have error number from first error, but you sho
uld be able to find out
error number for the others by some BOL searching (or searching though sysme
ssages).
--
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...[vbcol=seagreen]
> 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:21
349)
> could not be processed. See other errors for details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Ob
ject 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_da
ta_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 ther
e
> are errors and (2) repair the errors and (3) conditionally escalate to eve
r
> 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 n
ot
> 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/in.../>
pect_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...
> No, I do not recommend it. If you want more support to this standpoint, se
arch the archives, it is
> among others supported by MS dev team members.
>
> You search in Books Online for the specific error number. Each error numbe
r 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 jobs
tep and an Agent output
> file. Also, here you will find that most recommend restore from backup ins
tead of repair. In many
> cases, if you have log backups in place, you can end up with zero data los
s.
> For your specific problem, I'd Google, search KB and also the newsgroups a
rchives to see if there
> are other experiences with that particular error (or sequence or errors) t
hat has been vanishing.
> One problem is that you only have error number from first error, but you s
hould 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...
>|||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/in..._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...
search the archives, it is[vbcol=seagreen]
or not[vbcol=seagreen]
backup?[vbcol=seagreen]
number is documented with[vbcol=seagreen]
reports error number for first[vbcol=seagreen]
jobstep and an Agent output[vbcol=seagreen]
instead of repair. In many[vbcol=seagreen]
loss.[vbcol=seagreen]
archives to see if there[vbcol=seagreen]
that has been vanishing.[vbcol=seagreen]
should be able to find[vbcol=seagreen]
though sysmessages).[vbcol=seagreen]
has[vbcol=seagreen]
CHECKDB,[vbcol=seagreen]
actually[vbcol=seagreen]
way?[vbcol=seagreen]
SQL[vbcol=seagreen]
(1:21349)[vbcol=seagreen]
allocation[vbcol=seagreen]
allocation[vbcol=seagreen]
is the[vbcol=seagreen]
there[vbcol=seagreen]
ever[vbcol=seagreen]
next[vbcol=seagreen]
for[vbcol=seagreen]
or not[vbcol=seagreen]
backup?[vbcol=seagreen]
>|||the anti-virus example is a special case. You start SQL Server, but the AV s
w 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 erro
r 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, whi
ch
> 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 bett
er
> 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 shu
t
> 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 i
n
> message news:%23XG2rmfkFHA.3164@.TK2MSFTNGP15.phx.gbl...
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> in message
> search the archives, it is
> or not
> backup?
> number is documented with
> reports error number for first
> jobstep and an Agent output
> instead of repair. In many
> loss.
> archives to see if there
> that has been vanishing.
> should be able to find
> though sysmessages).
> has
> CHECKDB,
> actually
> way?
> SQL
> (1:21349)
> allocation
> allocation
> is the
> there
> ever
> next
> for
> or not
> backup?
>