Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shadetree66
Starting Member
1 Post |
 Posted - 2008-12-20 : 17:21:32
|
I have a database that has been put into suspect on sql 2000. I can put it into emergency (32768) and view everything in it. How would I go about repairing or removing the corruption. Of course, this is the only copy of the current database with no current backups. Here is the jist of what checkdb turned up.There are 150 consistency errors that follow the following feel:quote:
Server: Msg 8952, Level 16, State 1, Line 1Table error: Database '001', index 'BankTransactions.IX_timestamp' (ID 255833961) (index ID 25). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:410070:268) with values (bedrnr = '001' and timestamp = lžô4� and ID = 326522) points to the data row identified by ().Server: Msg 8952, Level 16, State 1, Line 1Table error: Database '001', index 'BankTransactions.IX_timestamp' (ID 255833961) (index ID 25). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:410070:269) with values (bedrnr = '001' and timestamp = lžô4� and ID = 326754) points to the data row identified by ().SCHECKDB found 0 allocation errors and 150 consistency errors in table 'BankTransactions' (object ID 255833961).
One Allocation error: quote:
Server: Msg 2576, Level 16, State 1, Line 1IAM page (0:0) is pointed to by the previous pointer of IAM page (1:1095428) object ID 639223415 index ID 2 but was not detected in the scan.CHECKDB found 1 allocation errors and 0 consistency errors in table 'Exact.AgingDistDate_93123AM' (object ID 639223415).
I even did a checkalloc and it turned up the allocation error.quote:
***************************************************************Table Exact.AgingDistDate_93123AM Object ID 639223415.Index ID 0. FirstIAM (1:1095426). Root (1:412330). Dpages 1212.Index ID 0. 1214 pages used in 152 dedicated extents.Total number of extents is 152.CHECKALLOC found 1 allocation errors and 0 consistency errors in table 'Exact.AgingDistDate_93123AM' (object ID 639223415).***************************************************************Total number of extents = 118849, used pages = 946180, reserved pages = 950789 in this database. (number of mixed extents = 850, mixed pages = 6797) in this database.CHECKALLOC found 1 allocation errors and 0 consistency errors in database '001'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKALLOC (001 ).DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I've tried using the repair_allow_data_loss but it doesn't seem to want to let me use it in suspect single user emergency database mode.I would love to repair the problems and get it out of suspect/emergency mode so it can be used again.Please help! |
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-12-23 : 15:25:37
|
| Paul and Gail, our resident "guru's on corruption" must be on holiday if you haven't received any responses by now. The bad news is "this is the only copy of the current database with no current backups" - never allow youself to be caught in this situation again....The good news is that you can get in and see your data. Based on the small section of the results from your DBCC, you might need to simply rebuild your indexes as that's where everything seems to be pointing (you did not post the entire result set so I cannot be certain), although a repair_allow_data_loss (which should really be a last-resort action), in theory should have fixed them. Or you can possibly extract all your data to a new database. Personally, I would take a backup of what you currently have before attempting anything, especially considering you can get in and see your data. Hopefully Paul or Gail will be along soon to give more expert advice. HTH.Terry |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-12-24 : 03:39:32
|
| Paul's on vacation until early Jan. I'm off from later today until Sat.Why no backup?What errors do you get when trying to run checkDB with repair in emergency mode?Can you have a look through the error log and see if you can find any messages that indicate why the DB was marked suspect? The errors you posted above are not enough for the DB to go suspect.Can you post the entire output of CheckDB please? I'd rather no offer advice on fixing without knowing all of the errors. It may be that one of the ones you left out completely changes the repair strategy.--Gail ShawSQL Server MVP |
|
|
|
tech_tiger
Starting Member
3 Posts |
Posted - 2009-02-12 : 03:49:14
|
| Hello, If problem is still not solved, you can go for data recovery software's. There are many excellent softwares available in the market... Search thoroughly the sql recovery software to diagonose, fix and repairs the errors. Definitely, sql recovery software will help you out...Good Luck!!!1 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 03:55:18
|
It looks to me that the index is corrupt, not the table itself.Gail, wouldn't it be possible to drop index and recreate it?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-12 : 05:29:07
|
quote:
Originally posted by Peso
It looks to me that the index is corrupt, not the table itself.Gail, wouldn't it be possible to drop index and recreate it?
Probably, if those are all of the errors, but I don't like to recommend fixes for corruption without seeing the entire CheckDB output. It just takes 1 error to change the recommendation from rebuild to repair to export and reload.That said, a drop-rebuild of the index won't fix the allocation error that's there. Also, a simple corruption in a NC index will not result in the DB been marked suspect. Either the error was encountered during recovery, or there's more going on. Either way, need more info to make a recommendation.--Gail ShawSQL Server MVP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 05:38:07
|
Thanks!
E 12°55'05.63"N 56°04'39.26" |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-02-12 : 05:38:33
|
quote:
Originally posted by tech_tiger
There are many excellent softwares available in the market...
And very few, if any, do a better job than SQL's built-in repair functionality, especially for repairable errors like these.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
SQL 2000 suspect database with allocation errors