Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you allI'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:
> Is there any possibility to correct such type of allocation problems without
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check for
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
>> Is there any possibility to correct such type of allocation problems
>> without
>> data loss?
>> After correction with ALLOW_DATA_LOSS option, is there any tool to check
>> for
>> referential integrity to discover witch records on witch tables
>> disppeared
>> with deallocations?
>> Thanks you all|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated by
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I obtained an unexpected result from the execution of CHECKDB with
REPAIR_REBUILD option.
0 allocation errors and 319 consistency errors found
All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
What did it happen? The 5 allocation error disappeared?
Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||Hi
After a DB has gone corrupt, and having 'fixed' it, I always DTS all the
data out into another DB. I don't trust the original DB anymore. Use the new
DB as the working one and the 'fixed' one as a reference in case you are
worried about data loss.
The problem with SAP is that it does not use FK constraints, so knowing if
all the data is there after a fix, is difficult.
If you have a backup from before the corruption, with all the logs to the
present, do a restore. Use this to verify how much data you have lost by
comparing it to your fixed DB.
At the same time, tell Finance that they have to sign-off on the reports
they run after you fix the DB. Then you have covered your ass, in case they
come to you next year and say "you know, you lost us more data than what we
though" and blame you for the company going bust.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||Sometimes a single error can 'hide' other errors by preventing a particular
set of checks running. In this case it seems like one of the previous
rebuild operations you performed fixed an allocation error that was
preventing the non-clustered index cross-checks from running (this check is
what found the 8952 and 8956 errors). This is completely expected behavior.
Without studying the initial list of errors from CHECKDB as well as your
database in the corrupted state, I can't tell the exact sequence of events
but what I've said seems the most likely (and you're not going to get a
better explanation from anyone else I'm afraid)
Now that you've run all these repairs, your database's inherent business
logic is likely to be broken unless you've taken pains to keep track of
exactly what's been done by repair.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||In a previous post I specified the initial 5 allocation errors, if it can be
useful for you...
The CHECKDB with repair_fast option fixed 1 more 8952/8956 error. Now I
launched a new CHECKDB (without any option) that will end in about 9 hours
from now.
I didn't understand the last part of your post. What do you mean when you
say: "...your database's inherent business logic is likely to be broken
unless you've taken pains to keep track of exactly what's been done by
repair..."?
I have printed all the subsequent errors found and fixed by the DBCC tool.
I kept track of the order of actions I executed so I can reapply them in the
production system step by step.
Is business logic most likely lost anyway? In this case I can't rely on this
solution because it's hard to verify SAP's business logic (see Mike Epprecht
post)
Thanks
Andrea
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>|||Finally the checkdb is OK. There are no errors. The complete sequence of
actions I executed is:
1) initial checkdb (5 allocation errors 89 consistency error)
(repair_allow_data_loss minimum required)
2) checktable (BSIS,repair_rebuild) 55 consistency error fixed
3) checktable (BSE_CLR,repair_rebuild) 28 consistency error fixed
4) checktable (BSAD,repair_rebuild) 6 consistency error fixed
5) checkdb with repair_rebuild 319 new consistency error fixed
6) checkdb 1 more consistency error found (repair_fast minimun required)
7) checkdb with repair_fast (1 consistency error fixed)
8) final checkdb (NO ERRORS)
As you can see I never used more than repair_rebuild level checks.
May I be sure I didn't lose any data?
Why you say that "...Now that you've run all these repairs, your database's
inherent business logic is likely to be broken ..."' What do you mean?
Thank you Paul
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>