DBCC CheckDB Repair_Rebuild caused data loss.

Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
...
Thanks,
Akin HocaI tried following DBCC command: DBCC CHECKDB (dadabase name, Repair_Rebuild)
not DBCC CHECKDB (dadabase name, REPAIR_FAST)
Thanks,
Akin
-- Akin wrote: --
Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
..
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
..
The error has been repaired.
(I believe 285 times.)
..
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
..
Thanks,
Akin Hoca|||You didn't lose any data as part of the repair. What happened was that you
had 285 forwarding records point to non-existent records.
When a heap record is updated such that it grows and there is no space for
the record to expand, it is moved to a new location in the heap. This new
location is called a forwarded record. The old location then contains a
pointer to the new location. The pointer in the old location is called a
forwarding record. Non-clustered indexes on heaps refer back to the heap
records using a physical record identifier (i.e. the file/page/slot of the
record). Using the forwarded row mechanism means that updates to heap
records do not cause updates to non-clustered indexes on the heap. It's an
optimization mechanism.
So, your heap had 285 of the pointer records in that did not point to
updated copies of the original record. These were deleted by repair and this
is allowed under repair_rebuild, because no data is being lost. The
non-clustered indexes were rebuilt because they would have referenced the
285 pointer records, and so must be rebuilt to remove those references.
Please let me know if that did not make sense.
Now, the more interesting question is how did this situation arise in the
first place? That I do not know and you may want to call PSS for assistance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akin" <anonymous@.discussions.microsoft.com> wrote in message
news:7E85386A-0102-4B30-8BF1-BB123342C065@.microsoft.com...
> I tried following DBCC command: DBCC CHECKDB (dadabase name,
Repair_Rebuild)
> not DBCC CHECKDB (dadabase name, REPAIR_FAST)
> Thanks,
> Akin
>
> -- Akin wrote: --
> Hi,
> I tried Repair_Rebuild option in 7.0 . I was thinking that the
following DBCC command would not cause any data loss, which also noted in
T-SQL References:
> DBCC CHECKDB (dadabase name, REPAIR_FAST)
> I lost 285 rows in a table that has total 103 thousand rows. (Thanks
to backups, I was able to get those rows back.) Is data loss possible while
using Repair_rebuild option?
> I got following messages:
> ...
> Server: Msg 8993, Level 16, State 1, Line 0
> Object ID 1656497080, forwarding row page (1:27122), slot 9 points to
page (1:242059), slot 14. Did not encounter forwarded row. Possible
allocation error.
> (I believe 285 times.)
> ...
> The error has been repaired.
> (I believe 285 times.)
> ...
> Repair: Deleted record for object ID 1656497080, index ID 0, on page
(1:27122), slot 9. Indexes will be rebuilt.
> (I believe 285 times.)
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in table
'tablename'. (object ID 1656497080).
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in
database 'databasename'.
> ...
> Thanks,
> Akin Hoca