Hello All,
Can I use dbcc dbreindex in a live database or do i need to put it
offline first?
thanx,
joel| Hello All,
|
| Can I use dbcc dbreindex in a live database or do i need to put it
| offline first?
--
Hi Joel,
You can use dbreindex live and you don't need to put the database in
single-user mode. Of course you should run this command during off-peak
hours so your users will not get a performance hit.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||thanks eric,
also i want to know the fillfactor to use in my database which is 3GB in
size and doing a lot of INSERt and UPDATE.
Eric Cardenas wrote:
> | Hello All,
> |
> | Can I use dbcc dbreindex in a live database or do i need to put it
> | offline first?
> --
> Hi Joel,
> You can use dbreindex live and you don't need to put the database in
> single-user mode. Of course you should run this command during off-peak
> hours so your users will not get a performance hit.
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||| thanks eric,
| also i want to know the fillfactor to use in my database which is 3GB in
| size and doing a lot of INSERTs and UPDATEs.
--
SQL Server Books Online says:
Note Even for an application oriented for many insert and update
operations, the number of database reads typically outnumber database
writes by a factor of 5 to 10. Therefore, specifying a fill factor other
than the default can degrade database read performance by an amount
inversely proportional to the fill factor setting. For example, a fill
factor value of 50 percent can cause database read performance to degrade
by two times.
It is useful to set the fill factor option to another value only when a new
index is created on a table with existing data, and then only when future
changes in that data can be accurately predicted.
Hope this helps,
--
Eric Cárdenas
SQL Server support
Showing posts with label live. Show all posts
Showing posts with label live. Show all posts
Saturday, February 25, 2012
Sunday, February 19, 2012
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?
>
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?
>
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/inf...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...
> 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.
>
> 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...
>
|||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/inf...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[vbcol=seagreen]
> 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?
>
|||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...
> http://www.karaszi.com/SQLServer/inf...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?
>
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/inf...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...
> 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.
>
> 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...
>
|||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/inf...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[vbcol=seagreen]
> 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?
>
|||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...
> http://www.karaszi.com/SQLServer/inf...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?
>
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
>> >>
>> >>
>> >
>
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
>> >>
>> >>
>> >
>
Subscribe to:
Posts (Atom)