Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

REPAIR_ALLOW_DATA_LOSS not resolving the allocation error Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 12:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 40, Visits: 568
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
Post #1351818
Posted Wednesday, August 29, 2012 12:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 23,002, Visits: 31,490
Looks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.



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)
Post #1351839
Posted Wednesday, August 29, 2012 12:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
Please run the following, post the full and unedited output.

DBCC CHECKDB (<Database Name>) 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 2008, MVP
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

Post #1351851
Posted Wednesday, August 29, 2012 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 40, Visits: 568
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





  Post Attachments 
pcprddb02 dbcc results.txt (9 views, 16.47 KB)
Post #1351858
Posted Wednesday, August 29, 2012 1:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:44 PM
Points: 358, Visits: 901
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.
Post #1351860
Posted Wednesday, August 29, 2012 2:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 40, Visits: 568
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
Post #1351901
Posted Wednesday, August 29, 2012 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
Please run the command I gave you and post the results.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1351903
Posted Wednesday, August 29, 2012 2:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1351911
Posted Wednesday, August 29, 2012 3:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 23,002, Visits: 31,490
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.



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)
Post #1351923
Posted Wednesday, August 29, 2012 3:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1351925
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse