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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40826 Visits: 38567
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).

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)
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
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
sierra4
sierra4
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 648
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90313 Visits: 45284
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

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


RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15314 Visits: 9518
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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90313 Visits: 45284
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, 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


RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15314 Visits: 9518
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."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90313 Visits: 45284
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, 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


karrylopez
karrylopez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
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