InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:21:23 GMT20http://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxAs 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 "[b]Kernel for Sql Server Repair[b][/b][/b]"You should be try this.Mon, 03 Sep 2012 07:03:07 GMTkarrylopezhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspx[quote][b]RBarryYoung (8/30/2012)[/b][hr]Didn't the OP say that this was Sql Server 2005? Can you still do something like that on 2005? (Yikes!!!)[/quote]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.Fri, 31 Aug 2012 00:06:13 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxGail:Didn't the OP say that this was Sql Server 2005? Can you still do something like that on 2005? (Yikes!!!)Thu, 30 Aug 2012 14:57:47 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxTo 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.Thu, 30 Aug 2012 13:33:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxYou 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:Thu, 30 Aug 2012 12:20:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxLooks 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.Thu, 30 Aug 2012 11:11:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxthe results forselect * from sys.indexes where object_id=1591012749name index_id [font="Courier New"]PK_EXAMPLEUsers 1 CLUSTERED 1IX_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[/font]i don't see index_id 22 which was specified in the dbcc resultsdoes this mean the table itself needs to be recreated?thanksThu, 30 Aug 2012 07:43:16 GMTsierra4http://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxgail,the complete output for DBCC CHECKDB (ExampleProd3x) WITH NO_INFOMSGS, ALL_ERRORMSGSisMsg 2576, Level 16, State 1, Line 1The 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).thanksThu, 30 Aug 2012 07:23:27 GMTsierra4http://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxNot 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 1The 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).Wed, 29 Aug 2012 15:14:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspx[quote][b]Lynn Pettis (8/29/2012)[/b][hr][quote][b]GilaMonster (8/29/2012)[/b][hr], but I would like a checkDB output without the info messages to confirm.[/quote]The results are posted above in an earlier post as an attachment.[/quote]Hence why I said "Without the info messages"Wed, 29 Aug 2012 15:12:37 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspx[quote][b]GilaMonster (8/29/2012)[/b][hr][quote][b]sierra4 (8/29/2012)[/b][hr]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?[/quote]In most cases, yes. It loses data[quote]i will recreate the indexes next.[/quote]Gut feel, that's not going to work here, but I would like a checkDB output without the info messages to confirm.[/quote]The results are posted above in an earlier post as an attachment.Wed, 29 Aug 2012 15:09:06 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspx[quote][b]sierra4 (8/29/2012)[/b][hr]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?[/quote]In most cases, yes. It loses data[quote]i will recreate the indexes next.[/quote]Gut feel, that's not going to work here, but I would like a checkDB output without the info messages to confirm.Wed, 29 Aug 2012 14:30:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxPlease run the command I gave you and post the results.Wed, 29 Aug 2012 14:10:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxsorry 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 youWed, 29 Aug 2012 14:02:09 GMTsierra4http://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspx[quote][b]Lynn Pettis (8/29/2012)[/b][hr]Looks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.[/quote]Just as an FYI, I would have done this [i]before [/i]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 [i]all [/i]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.Wed, 29 Aug 2012 13:00:40 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxI'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 outputWed, 29 Aug 2012 12:59:00 GMTsierra4http://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxPlease run the following, post the full and unedited output.[code]DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS[/code]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.Wed, 29 Aug 2012 12:49:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxLooks like a problem with a nonclustered index, id = 22. Try dropping and recreating this index.Wed, 29 Aug 2012 12:41:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1351818-265-1.aspxhello, 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_ERRORMSGSMsg 2576, Level 16, State 1, Line 1The 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 = 1we'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 1The 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 youWed, 29 Aug 2012 12:15:29 GMTsierra4