August 29, 2012 at 12:15 pm
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
August 29, 2012 at 12:41 pm
Looks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.
August 29, 2012 at 12:49 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2012 at 12:59 pm
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
August 29, 2012 at 1:00 pm
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.
August 29, 2012 at 2:02 pm
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
August 29, 2012 at 2:10 pm
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
August 29, 2012 at 2:30 pm
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
August 29, 2012 at 3:09 pm
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.
August 29, 2012 at 3:12 pm
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
August 29, 2012 at 3:14 pm
Not that much in there really and the following is the only error I see:
DBCC results for 'EXAMPLEUsers'.
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.
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).
August 30, 2012 at 7:23 am
gail,
the complete output for
DBCC CHECKDB (ExampleProd3x) WITH NO_INFOMSGS, ALL_ERRORMSGS
is
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).
thanks
August 30, 2012 at 7:43 am
the results for
select * from sys.indexes where object_id=1591012749
name index_id
[font="Courier New"]PK_EXAMPLEUsers 1 CLUSTERED1
IX_EXAMPLEUsers_clientid 7 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__1K 8 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__2K 14 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__3K 15 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__4K 16 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__5K 18 NONCLUSTERED0
_dta_index_EXAMPLEUsers_9_1591012749__6K 20 NONCLUSTERED0[/font]
i don't see index_id 22 which was specified in the dbcc results
does this mean the table itself needs to be recreated?
thanks
August 30, 2012 at 11:11 am
Looks like either someone's been messing with the metadata or there's been some odd problem dropping an index somewhere. The index structure still exists but the metadata's gone.
Recreating the table might help (select into, drop the old table, rename the new one, recreate all indexes). If it doesn't, you'll need to recreate the database.
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
August 30, 2012 at 12:20 pm
You know, instead of "REPAIR_ALLOW_DATA_LOSS", they should have named that option "RISK_SHOOTING_YOURSELF_IN_THE_FOOT", or maybe "DO_NOT_USE_UNLESS_YOU_REALLY_KNOW_WTF_YOU_ARE_DOING".
:crazy:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply