Consistency errors/repair_all_data_loss question

I've been receiving consistency errors for the past few weeks
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
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/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegroups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
>>From the info below, can you tell if the only thing that changed was
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>