dbcc repair_allow_data_loss

Hi newsgroup,
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were d
etected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors
were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with
the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR
_XXXXX option more
than one time.
Thank you & Regards,
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
...
Just to be sure: You mean, that it's possible that data is lost and we do no
t have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the
suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe|||If you do regular transaction log backups, you can do a log backup after you
discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably
, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my
general recommendations, btw:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have
any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the sugges
tion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.

> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.

> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>sql

dbcc repair_allow_data_loss

Hi newsgroup,
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were detected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option more
than one time.
Thank you & Regards,
Sven Krampe
Hi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe
|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
....
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe
|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.

> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.

> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>

dbcc repair_allow_data_loss

Hi newsgroup
in the last days we had a lot of trouble with our SQL-Server2000 database
We moved to complete new Hardware. Now it's solved (i hope so)
but we want to be prepared for the future. Therefore i've a few questions
During the normal maintenance of the database some consistency errors were detected
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb
We had the luck, that not data loss was mentioned in the log file
So, can we be really sure that there was no data loss
If there's a data loss, how detailed is the information about the lost data
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected
The check suggested the REPAIR_REBUILD option
For me the question is, why are there still errors after the first run with th
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option mor
than one time
Thank you & Regards
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume tha
data was lost if you had to use this option and so your business o
application logic may no longer be correct (constraints etc)
...
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job
We check our database every night
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)
Go back to the last complete backup? (lose 24 h work of the users)
Have a system downtime and try to repair the database
Thank you very much for your help
Regards
Sven Kramp|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

Hi
I ran a DBCC ceckdb with repair data loss
Is there anyway to know what data has been repaired ?
moreover if some data is lost how to find what is lost?
ThanksNot really. That is why it is so important to have proper backups that you
can restore from so as to minimize data loss.
Andrew J. Kelly
SQL Server MVP
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>|||Thanks Andrew|||Hi -
There is no easy way to find this out. If REPAIR_ALLOW_DATA_LOSS is
specified, many errors are "fixed" simply by de-allocating the 8k SQL page.
Thanks
- Surajit
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

Hi
I ran a DBCC ceckdb with repair data loss
Is there anyway to know what data has been repaired ?
moreover if some data is lost how to find what is lost?
Thanks
Not really. That is why it is so important to have proper backups that you
can restore from so as to minimize data loss.
Andrew J. Kelly
SQL Server MVP
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>
|||Thanks Andrew
|||Hi -
There is no easy way to find this out. If REPAIR_ALLOW_DATA_LOSS is
specified, many errors are "fixed" simply by de-allocating the 8k SQL page.
Thanks
- Surajit
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>

DBCC CHECKDB

Hello NG,
I need some assistance in the subject "repairing".
I always thought,
DBCC CHECKDB (' dbname ', REPAIR_ALLOW_DATA_LOSS)
would be the worst, which one could do to an SQL server data base.
But our installation (8.00.760 = SQL2000 with SP3 on W2000Server)
refuses accomplishing the repair even with a maximum loss and
answers:
[ 4 ] Data base XenoxDB: Data and
Indexverkn?pfung?berpr?fen... [ Microsoft SQL DMO (ODBC SQLState:
42000) ] Error 8929: [ Microsoft][ODBC SQL server Driver][SQL
Server]Objekt ID 2: Error in text ID 195789324288 found, their owner
data record RID = (1:1317:3) ID = 861506398 and indid = 1 is. [
Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
ID 2. The text -, ntext or image knots on side (1:5999), Slot 0, text
ID 195789324288 does not tune with its reference from side (1:10742),
Slot 10?berein. [ Microsoft][ODBC SQL server Driver][SQL
Server]Tabellenfehler: Object ID 2. The text -, ntext or image knots
on side (1:10742), Slot 10, text ID 58350370816 does not tune with its
reference from side (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL
server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
caused that this elimination of errors became to?bergangen. [
Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
the DBCC instruction caused that this elimination of errors became
to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL server ] the RH
Pa IR stage in the DBCC instruction caused that this elimination of
errors became to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL
Server]CHECKDB found 0 reservation errors and 3 consistency error in
the sysindexes table (object ID 2). [ Microsoft][ODBC SQL server
Driver][SQL Server]CHECKDB found 0 reservation errors and 3
consistency error in the XenoxDB data base.
The following errors arose:
[ Microsoft][ODBC SQL server Driver][SQL Server]Objekt ID 2: Error in
text ID 195789324288 found, their owner data record RID = (1:1317:3)
ID = 861506398 and indid = 1 is. [ Microsoft][ODBC SQL server
Driver][SQL Server]Tabellenfehler: Object ID 2. The text -, ntext or
image knots on side (1:5999), Slot 0, text ID 195789324288 does not
tune with its reference from side (1:10742), Slot 10?berein. [
Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
ID 2. The text -, ntext or image knots on side (1:10742), Slot 10,
text ID 58350370816 does not tune with its reference from side
(1:1317), Slot 3?berein. [ Microsoft][ODBC SQL server Driver][SQL
server ] the RH Pa IR stage in the DBCC instruction caused that this
elimination of errors became to?bergangen. [ Microsoft][ODBC SQL
server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
caused that this elimination of errors became to?bergangen. [
Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
the DBCC instruction caused that this elimination of errors became
to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL Server]CHECKDB
found 0 reservation errors and 3 consistency error in the sysindexes
table (object ID 2). [ Microsoft][ODBC SQL server Driver][SQL
Server]CHECKDB found 0 reservation errors and 3 consistency error in
the XenoxDB data base. ** Ausf?hrungsdauer: 0 Std, 0 min, 3 sec. **
Someone an idea, how to repair the index nevertheless - perhaps
also on detours?
Best Regards
Thomas
Thomas,
I assume that you went through the process describe in the Books Online for
error 8929. At the end, after checking hardware, restoring a good backup,
and running DBCC with the appropriate repair option, it says:
"If running DBCC CHECKDB with one of the repair clauses does not correct the
problem, contact your primary support provider."
Russell Fields
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:4124ADCB.4080006@.rwth-aachen.de...
> Hello NG,
> I need some assistance in the subject "repairing".
> I always thought,
> --
> DBCC CHECKDB (' dbname ', REPAIR_ALLOW_DATA_LOSS)
> --
> would be the worst, which one could do to an SQL server data base.
> But our installation (8.00.760 = SQL2000 with SP3 on W2000Server)
> refuses accomplishing the repair even with a maximum loss and
> answers:
> --
> [ 4 ] Data base XenoxDB: Data and
> Indexverkn?pfung?berpr?fen... [ Microsoft SQL DMO (ODBC SQLState:
> 42000) ] Error 8929: [ Microsoft][ODBC SQL server Driver][SQL
> Server]Objekt ID 2: Error in text ID 195789324288 found, their owner
> data record RID = (1:1317:3) ID = 861506398 and indid = 1 is. [
> Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
> ID 2. The text -, ntext or image knots on side (1:5999), Slot 0, text
> ID 195789324288 does not tune with its reference from side (1:10742),
> Slot 10?berein. [ Microsoft][ODBC SQL server Driver][SQL
> Server]Tabellenfehler: Object ID 2. The text -, ntext or image knots
> on side (1:10742), Slot 10, text ID 58350370816 does not tune with its
> reference from side (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL
> server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
> caused that this elimination of errors became to?bergangen. [
> Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
> the DBCC instruction caused that this elimination of errors became
> to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL server ] the RH
> Pa IR stage in the DBCC instruction caused that this elimination of
> errors became to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL
> Server]CHECKDB found 0 reservation errors and 3 consistency error in
> the sysindexes table (object ID 2). [ Microsoft][ODBC SQL server
> Driver][SQL Server]CHECKDB found 0 reservation errors and 3
> consistency error in the XenoxDB data base.
> The following errors arose:
> [ Microsoft][ODBC SQL server Driver][SQL Server]Objekt ID 2: Error in
> text ID 195789324288 found, their owner data record RID = (1:1317:3)
> ID = 861506398 and indid = 1 is. [ Microsoft][ODBC SQL server
> Driver][SQL Server]Tabellenfehler: Object ID 2. The text -, ntext or
> image knots on side (1:5999), Slot 0, text ID 195789324288 does not
> tune with its reference from side (1:10742), Slot 10?berein. [
> Microsoft][ODBC SQL server Driver][SQL Server]Tabellenfehler: Object
> ID 2. The text -, ntext or image knots on side (1:10742), Slot 10,
> text ID 58350370816 does not tune with its reference from side
> (1:1317), Slot 3?berein. [ Microsoft][ODBC SQL server Driver][SQL
> server ] the RH Pa IR stage in the DBCC instruction caused that this
> elimination of errors became to?bergangen. [ Microsoft][ODBC SQL
> server Driver][SQL server ] the RH Pa IR stage in the DBCC instruction
> caused that this elimination of errors became to?bergangen. [
> Microsoft][ODBC SQL server Driver][SQL server ] the RH Pa IR stage in
> the DBCC instruction caused that this elimination of errors became
> to?bergangen. [ Microsoft][ODBC SQL server Driver][SQL Server]CHECKDB
> found 0 reservation errors and 3 consistency error in the sysindexes
> table (object ID 2). [ Microsoft][ODBC SQL server Driver][SQL
> Server]CHECKDB found 0 reservation errors and 3 consistency error in
> the XenoxDB data base. ** Ausf?hrungsdauer: 0 Std, 0 min, 3 sec. **
> Someone an idea, how to repair the index nevertheless - perhaps
> also on detours?
> Best Regards
> Thomas
>
|||Russel,
I would have been the happiest man alive, if Id found any of that
books, you mention.
I didnt find any suggestions, how to get rid of errror 8929 :-(
I checked hardware, but the restore of a good backup would cause the
same data loss, that checkdb threatens with.
So, the contact your provider-option seemed to be the "last resort" -
but the answers of MS Germany didnt help us at all :-((
Best Regards
Thomas
Russell Fields schrieb:

>Thomas,
>I assume that you went through the process describe in the Books Online for
>error 8929. At the end, after checking hardware, restoring a good backup,
>and running DBCC with the appropriate repair option, it says:
>"If running DBCC CHECKDB with one of the repair clauses does not correct the
>problem, contact your primary support provider."
>Russell Fields
>"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
>news:4124ADCB.4080006@.rwth-aachen.de...
>
>
>
|||Have you tried running checkdb several times in succession? There are some
error combinations that the SQL Server 2000 repair isn't able to fix on the
first attempt (as other errors prvent the fix from running). I don't see
anything in the output you've posted that would suggest checkdb would have a
problem repairing these. Can you post the output from repair?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:4124BADD.4040502@.rwth-aachen.de...[vbcol=seagreen]
> Russel,
> I would have been the happiest man alive, if Id found any of that
> books, you mention.
> I didnt find any suggestions, how to get rid of errror 8929 :-(
> I checked hardware, but the restore of a good backup would cause the
> same data loss, that checkdb threatens with.
> So, the contact your provider-option seemed to be the "last resort" -
> but the answers of MS Germany didnt help us at all :-((
> Best Regards
> Thomas
>
> Russell Fields schrieb:
for[vbcol=seagreen]
backup,[vbcol=seagreen]
the
>
|||Paul S Randal [MS] schrieb:
> Have you tried running checkdb several times in succession? There are some
> error combinations that the SQL Server 2000 repair isn't able to fix on the
> first attempt (as other errors prvent the fix from running). I don't see
> anything in the output you've posted that would suggest checkdb would have a
> problem repairing these. Can you post the output from repair?
> Regards.
>
Paul,
hope youre still watching, so you can see what our SQL Server did
to me. I dont know, how many times I ran the procedures in
succussion :-(
Best Regards
Thomas
FYI:
--CHECKDB without repair--
Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
seinem Verweis von Seite (1:10742), Slot 10 berein.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
seinem Verweis von Seite (1:1317), Slot 3 berein.
DBCC-Ergebnis fr 'XenoxDB'.
DBCC-Ergebnis fr 'sysobjects'.
Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
DBCC-Ergebnis fr 'sysindexes'.
Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
sysindexes-Tabelle gefunden (Objekt-ID 2).
DBCC-Ergebnis fr 'syscolumns'.
Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
DBCC-Ergebnis fr 'systypes'.
Es sind 26 Zeilen in 1 Seiten fr das systypes-Objekt vorhanden.
DBCC-Ergebnis fr 'syscomments'.
Es sind 2828 Zeilen in 63 Seiten fr das syscomments-Objekt vorhanden.
----and so on ...
--CHECKDB repair_allow_data_loss--
Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
seinem Verweis von Seite (1:10742), Slot 10 berein.
Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
seinem Verweis von Seite (1:1317), Slot 3 berein.
DBCC-Ergebnis fr 'XenoxDB'.
DBCC-Ergebnis fr 'sysobjects'.
Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
DBCC-Ergebnis fr 'sysindexes'.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
diese Fehlerbehebung bergangen wurde.
Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
sysindexes-Tabelle gefunden (Objekt-ID 2).
DBCC-Ergebnis fr 'syscolumns'.
Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
DBCC-Ergebnis fr 'systypes'.
--Rest is similar to above output !
|||Ah - the errors are in sysindexes so that may be stopping from repairing
them. You're going to need to call Product Support to help you as we can't
do this through Usenet.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas Prost" <thomas.prost@.rwth-aachen.de> wrote in message
news:41347038.8040909@.rwth-aachen.de...[vbcol=seagreen]
> Paul S Randal [MS] schrieb:
some[vbcol=seagreen]
the[vbcol=seagreen]
have a
> Paul,
> hope youre still watching, so you can see what our SQL Server did
> to me. I dont know, how many times I ran the procedures in
> succussion :-(
> Best Regards
> Thomas
>
> FYI:
> --CHECKDB without repair--
> Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
> Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
> Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
> seinem Verweis von Seite (1:10742), Slot 10 berein.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
> seinem Verweis von Seite (1:1317), Slot 3 berein.
> DBCC-Ergebnis fr 'XenoxDB'.
> DBCC-Ergebnis fr 'sysobjects'.
> Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
> DBCC-Ergebnis fr 'sysindexes'.
> Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
> CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
> sysindexes-Tabelle gefunden (Objekt-ID 2).
> DBCC-Ergebnis fr 'syscolumns'.
> Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
> DBCC-Ergebnis fr 'systypes'.
> Es sind 26 Zeilen in 1 Seiten fr das systypes-Objekt vorhanden.
> DBCC-Ergebnis fr 'syscomments'.
> Es sind 2828 Zeilen in 63 Seiten fr das syscomments-Objekt vorhanden.
> ----and so on ...
>
> --CHECKDB repair_allow_data_loss--
> Server: Nachr.-Nr. 8929, Schweregrad 16, Status 1, Zeile 3
> Objekt-ID 2: Fehler in Text-ID 195789324288 gefunden, deren
> Besitzer Datensatz RID = (1:1317:3) id = 861506398 and indid = 1 ist.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:5999), Slot 0, Text-ID 195789324288 stimmt nicht mit
> seinem Verweis von Seite (1:10742), Slot 10 berein.
> Server: Nachr.-Nr. 8961, Schweregrad 16, Status 1, Zeile 3
> Tabellenfehler: Objekt-ID 2. Der text-, ntext- oder image-Knoten
> auf Seite (1:10742), Slot 10, Text-ID 58350370816 stimmt nicht mit
> seinem Verweis von Seite (1:1317), Slot 3 berein.
> DBCC-Ergebnis fr 'XenoxDB'.
> DBCC-Ergebnis fr 'sysobjects'.
> Es sind 3908 Zeilen in 71 Seiten fr das sysobjects-Objekt vorhanden.
> DBCC-Ergebnis fr 'sysindexes'.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Die REPAIR-Stufe in der DBCC-Anweisung hat bewirkt, dass
> diese Fehlerbehebung bergangen wurde.
> Es sind 4919 Zeilen in 276 Seiten fr das sysindexes-Objekt vorhanden.
> CHECKDB hat 0 Reservierungsfehler und 3 Konsistenzfehler in der
> sysindexes-Tabelle gefunden (Objekt-ID 2).
> DBCC-Ergebnis fr 'syscolumns'.
> Es sind 8733 Zeilen in 192 Seiten fr das syscolumns-Objekt vorhanden.
> DBCC-Ergebnis fr 'systypes'.
> --Rest is similar to above output !
>

DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS...

Friends,
A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
discovering inconsistencies after a catastrophic power failure. (They
didn't even suspect trouble until weeks later, and at this point all backups
contain the inconsistencies.) The DBCC command now completes without
errors, but we worry that data (actual "row" data, not just index data)
might have been lost. We also worry about resulting integrity problems,
orphaned foreign key references, etc.
It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
be the command we are looking for. If we understand things correctly, it
will report rows that violate some constraint.
Is our thinking correct here? Will system tables and constraints be checked
too? Is there anything else we should be concerned about? (Other than lost
business data, and a UPS for the customer, of course.)
Any words, links, and thoughts of your will be very much appreciated.
Thanks in advance for your time!
James Hunter Ross
Senior Software Developer
O'Neil Software, Inc.Hi
DBCC CHECKCONSTRAINTS will check the integrity of CHECK constraints and FKs
wrt to the data that is present, but it won't tell you if consistencies not
enforced in the database are still correct or if both referenced/referencing
data is missing. If you have copies of historical reports you may want to ru
n
them to compare what they returned previously and now.
John
"James Hunter Ross" wrote:

> Friends,
> A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
> discovering inconsistencies after a catastrophic power failure. (They
> didn't even suspect trouble until weeks later, and at this point all backu
ps
> contain the inconsistencies.) The DBCC command now completes without
> errors, but we worry that data (actual "row" data, not just index data)
> might have been lost. We also worry about resulting integrity problems,
> orphaned foreign key references, etc.
> It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
> be the command we are looking for. If we understand things correctly, it
> will report rows that violate some constraint.
> Is our thinking correct here? Will system tables and constraints be check
ed
> too? Is there anything else we should be concerned about? (Other than lo
st
> business data, and a UPS for the customer, of course.)
> Any words, links, and thoughts of your will be very much appreciated.
> Thanks in advance for your time!
> James Hunter Ross
> Senior Software Developer
> O'Neil Software, Inc.
>
>

DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS...

Friends,
A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
discovering inconsistencies after a catastrophic power failure. (They
didn't even suspect trouble until weeks later, and at this point all backups
contain the inconsistencies.) The DBCC command now completes without
errors, but we worry that data (actual "row" data, not just index data)
might have been lost. We also worry about resulting integrity problems,
orphaned foreign key references, etc.
It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
be the command we are looking for. If we understand things correctly, it
will report rows that violate some constraint.
Is our thinking correct here? Will system tables and constraints be checked
too? Is there anything else we should be concerned about? (Other than lost
business data, and a UPS for the customer, of course.)
Any words, links, and thoughts of your will be very much appreciated.
Thanks in advance for your time!
James Hunter Ross
Senior Software Developer
O'Neil Software, Inc.Hi
DBCC CHECKCONSTRAINTS will check the integrity of CHECK constraints and FKs
wrt to the data that is present, but it won't tell you if consistencies not
enforced in the database are still correct or if both referenced/referencing
data is missing. If you have copies of historical reports you may want to run
them to compare what they returned previously and now.
John
"James Hunter Ross" wrote:
> Friends,
> A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
> discovering inconsistencies after a catastrophic power failure. (They
> didn't even suspect trouble until weeks later, and at this point all backups
> contain the inconsistencies.) The DBCC command now completes without
> errors, but we worry that data (actual "row" data, not just index data)
> might have been lost. We also worry about resulting integrity problems,
> orphaned foreign key references, etc.
> It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
> be the command we are looking for. If we understand things correctly, it
> will report rows that violate some constraint.
> Is our thinking correct here? Will system tables and constraints be checked
> too? Is there anything else we should be concerned about? (Other than lost
> business data, and a UPS for the customer, of course.)
> Any words, links, and thoughts of your will be very much appreciated.
> Thanks in advance for your time!
> James Hunter Ross
> Senior Software Developer
> O'Neil Software, Inc.
>
>

DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS...

Friends,
A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
discovering inconsistencies after a catastrophic power failure. (They
didn't even suspect trouble until weeks later, and at this point all backups
contain the inconsistencies.) The DBCC command now completes without
errors, but we worry that data (actual "row" data, not just index data)
might have been lost. We also worry about resulting integrity problems,
orphaned foreign key references, etc.
It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
be the command we are looking for. If we understand things correctly, it
will report rows that violate some constraint.
Is our thinking correct here? Will system tables and constraints be checked
too? Is there anything else we should be concerned about? (Other than lost
business data, and a UPS for the customer, of course.)
Any words, links, and thoughts of your will be very much appreciated.
Thanks in advance for your time!
James Hunter Ross
Senior Software Developer
O'Neil Software, Inc.
Hi
DBCC CHECKCONSTRAINTS will check the integrity of CHECK constraints and FKs
wrt to the data that is present, but it won't tell you if consistencies not
enforced in the database are still correct or if both referenced/referencing
data is missing. If you have copies of historical reports you may want to run
them to compare what they returned previously and now.
John
"James Hunter Ross" wrote:

> Friends,
> A customer of ours had to do a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS after
> discovering inconsistencies after a catastrophic power failure. (They
> didn't even suspect trouble until weeks later, and at this point all backups
> contain the inconsistencies.) The DBCC command now completes without
> errors, but we worry that data (actual "row" data, not just index data)
> might have been lost. We also worry about resulting integrity problems,
> orphaned foreign key references, etc.
> It appears that the command DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS might
> be the command we are looking for. If we understand things correctly, it
> will report rows that violate some constraint.
> Is our thinking correct here? Will system tables and constraints be checked
> too? Is there anything else we should be concerned about? (Other than lost
> business data, and a UPS for the customer, of course.)
> Any words, links, and thoughts of your will be very much appreciated.
> Thanks in advance for your time!
> James Hunter Ross
> Senior Software Developer
> O'Neil Software, Inc.
>
>