SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


REPAIR_ALLOW_DATA_LOSS not resolving the allocation error


REPAIR_ALLOW_DATA_LOSS not resolving the allocation error

Author
Message
sierra4
sierra4
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 678
hello,

this is on sql server 2005.
it is for a new client. this may be the first time dbcc checkdb was ran on the databases so the error may have been there for some time.

executed the following:
DBCC CHECKDB (ExampleProd3x) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:3878384) in object ID 1591012749, index ID 22, partition ID 72057594103005184, alloc unit ID 72057594108379136 (type In-row data), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table 'EXAMPLEUsers' (object ID 1591012749).
CHECKDB found 1 allocation errors and 0 consistency errors in database 'ExampleProd3x'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ExampleProd3x).


i can select from the table (select *, and select with where clause) just fine.


from: DBCC PAGE('ExampleProd3x', 1, 913345, 3)
i get: Metadata: IndexId = 1


we've done the repair:
dbcc checkdb ('ExampleProd3x', REPAIR_ALLOW_DATA_LOSS)

DBCC results for 'EXAMPLEUsers'.
Repair: IAM chain for object ID 1591012749, index ID 22, partition ID 72057594103005184, alloc unit ID 72057594108379136 (type In-row data), has been truncated before page (1:3878384) and will be rebuilt.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:3878384) in object ID 1591012749, index ID 22, partition ID 72057594103005184, alloc unit ID 72057594108379136 (type In-row data), but it was not detected in the scan.
The error has been repaired.
There are 29537 rows in 697 pages for object "EXAMPLEUsers".
CHECKDB found 1 allocation errors and 0 consistency errors in table 'EXAMPLEUsers' (object ID 1591012749).
CHECKDB fixed 1 allocation errors and 0 consistency errors in table 'EXAMPLEUsers' (object ID 1591012749).

This says the error has been repaired.

but when we run dbcc checkdb again it still fails on the same table/object:
CHECKDB found 1 allocation errors and 0 consistency errors in table 'EXAMPLEUsers' (object ID 1591012749).


What other methods are there to resolve the allocation error?

the table only has 30k rows, though there are some dependencies, if it's an option to rebuild this or the indexes.

thank you
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96439 Visits: 38981
Looks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228427 Visits: 46342
Please run the following, post the full and unedited output.


DBCC CHECKDB (<Database Name>Wink WITH NO_INFOMSGS, ALL_ERRORMSGS



CheckDB never guarantees to repair all errors. It's primarily (though certainly not only) for times when you don't have a backup and have no choice but to repair and discard data in the process.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sierra4
sierra4
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 678
I've attached the complete dbcc results.



for the index with id 22, i can't find which one this is.

SELECT name from sysindexes where id = 1591012749 and indid = 22;
=> no output
Attachments
pcprddb02 dbcc results.txt (57 views, 16.00 KB)
Scott D. Jacobson
Scott D. Jacobson
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1368 Visits: 1020
Lynn Pettis (8/29/2012)
Looks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.


Just as an FYI, I would have done this before using REPAIR_ALLOW_DATA_LOSS. It does indeed look like an index that can be dropped and re-created and considering the severity of REPAIR_ALLOW_DATA_LOSS trying that first makes more sense. If that didn't work, I still would have gone to backups. Maybe you'll get lucky and they're not all corrupted.

EDIT: You also posted this to the wrong forum ( SQL Server 7,2000 but you said it's 2005). Could have caused confusion for some.
sierra4
sierra4
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 678
sorry wrong forum, my bad.

this db is restored in test, so i am trying out the dbcc repair_allow_data_loss first in the test area.

I am unable to determine which index this is based on the given index id and object id, or perhaps i was querying from the wrong tables(?). though this shouldn't be a problem since there are only 2 indexes anyway, except i would like to understand what the error is referring to if i cannot pinpoint the object.

i have not used the dbcc repair options before, but in this regard is the experts' recommendation to use dbcc repair only as a last resort?

i will recreate the indexes next.

thank you
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228427 Visits: 46342
Please run the command I gave you and post the results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228427 Visits: 46342
sierra4 (8/29/2012)
i have not used the dbcc repair options before, but in this regard is the experts' recommendation to use dbcc repair only as a last resort?


In most cases, yes. It loses data

i will recreate the indexes next.


Gut feel, that's not going to work here, but I would like a checkDB output without the info messages to confirm.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96439 Visits: 38981
GilaMonster (8/29/2012)
sierra4 (8/29/2012)
i have not used the dbcc repair options before, but in this regard is the experts' recommendation to use dbcc repair only as a last resort?


In most cases, yes. It loses data

i will recreate the indexes next.


Gut feel, that's not going to work here, but I would like a checkDB output without the info messages to confirm.


The results are posted above in an earlier post as an attachment.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228427 Visits: 46342
Lynn Pettis (8/29/2012)
GilaMonster (8/29/2012)
, but I would like a checkDB output without the info messages to confirm.


The results are posted above in an earlier post as an attachment.


Hence why I said "Without the info messages"

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search