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 3:14 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:12 PM
Points: 23,397, Visits: 32,241
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).




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 #1351927
Posted Thursday, August 30, 2012 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:38 AM
Points: 40, Visits: 569
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

Post #1352204
Posted Thursday, August 30, 2012 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:38 AM
Points: 40, Visits: 569
the results for
select * from sys.indexes where object_id=1591012749

name index_id
PK_EXAMPLEUsers 1 CLUSTERED 1
IX_EXAMPLEUsers_clientid 7 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__1K 8 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__2K 14 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__3K 15 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__4K 16 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__5K 18 NONCLUSTERED 0
_dta_index_EXAMPLEUsers_9_1591012749__6K 20 NONCLUSTERED 0


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
Post #1352223
Posted Thursday, August 30, 2012 11:11 AM


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 @ 12:15 PM
Points: 43,017, Visits: 36,179
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 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 #1352380
Posted Thursday, August 30, 2012 12:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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".




-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1352413
Posted Thursday, August 30, 2012 1:33 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 @ 12:15 PM
Points: 43,017, Visits: 36,179
To be honest, this looks more like it could have been caused by someone doing a DELETE FROM sysindexes WHERE... on SQL 2000 (if the DB was an upgrade)

I've seen an IAM lose it's link to the metadata before, but not a case where all of the index metadata was completely gone.



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 #1352463
Posted Thursday, August 30, 2012 2:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Gail:

Didn't the OP say that this was Sql Server 2005? Can you still do something like that on 2005? (Yikes!!!)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1352510
Posted Friday, August 31, 2012 12:06 AM


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 @ 12:15 PM
Points: 43,017, Visits: 36,179
RBarryYoung (8/30/2012)
Didn't the OP say that this was Sql Server 2005? Can you still do something like that on 2005? (Yikes!!!)


Yes, it is actually still possible, though I'm not going say how.

DB may be SQL 2005 now, but if it was upgraded from SQL 2000 it could be someone messed with it there. Or it could just be some weird-*** corruption.



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 #1352605
Posted Monday, September 3, 2012 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:48 AM
Points: 4, Visits: 2
As per your discuss and all reply i think so that now your data is corrupted and you should be used a third party data recovery software.

There are so many Sql server related data recovery software you can find it on Google just search as "repair sql database" and you can find a large list of software you can one of them .

As I have personal experience with a data recovery software which was really helpful for me if you want i can recommended for which is "Kernel for Sql Server Repair[b][/b]"

You should be try this.
Post #1353499
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse