September 26, 2006 at 2:26 pm
Last week we had quite an issue with our RAID array, but we ended up restoring it and managed to pull the database off it. Everything looked Ok on it, however, we've been having problems with one table in the database since then. The table was reporting errors at first, so we ended up having to do a repair allowing data loss (nothing else seemed to work). We also rebuilt the index on the table. The table then seemed fine, I've run DBCC CHECKDB, CHECKALLOC on the DB, CHECKTABLE on the table, etc. but nothing reports errors.
We've had erratic problems since then though as well. When trying to delete rows from the table, it gives this error message to the user: "Unable to find index entry in index ID 1, of table 1175675236, in database
'OurDB'. The indicated index is corrupt or there is a problem with
the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem
persists, contact product support.
A severe error occurred on the current command. The results, if any, should
be discarded."
This is after we rebuilt the index. Also, I've found that there are "phantom" rows in the table. For instance, "SELECT * FROM tblX WHERE ID IN (101,102,103,104)" returns 0 rows, however, "SELECT COUNT(*) FROM tblX WHERE ID IN (101,102,103,104)" returns "4" as it's result, so it CAN see the rows. I then realized that you could select the first 2 columns in the table (18 columns total), and it will return the values, but if you add any of the other 16 columns, it doesn't return anything. The first column is the primary key, and it's datatype is bigint. The second column is just a varchar(50) with some misc. info in it.
I am very lost at this point, as I've tried as much as I can think of, but haven't been able to figure out what to do to remedy this issue. Any help or tips will be greatly appreciated!
Thanks!
September 27, 2006 at 2:31 am
Your only option might be to restore a backup taken before your RAID problems, then, depending on urgency, either:
1. Attempt to synchronise the data between the 'good' tables in the existing database and the newly restored database and accept that data inserted into your corrupt table since the backup has been lost. (You could use Red-Gate Software's 'SQL Data Compare', or similar, to quickly synchronise the data).
2. If maintaining data integrity means that you cannot allow loss of data in the corrupt table then you'll have to accept that data inserted in the time period since the backup has been lost.
Neither is ideal but if you have got a pre-failure backup then at least you can salvage something. If you don't have a backup then now would be a good time to implement a robust backup strategy.
Good luck!
Chris
September 27, 2006 at 7:21 am
I really wish I would have just restored the backup, it was made 3 hours before the RAID gave out, so it wouldn't have been the end of the world to start from there. When we got the RAID back up and the database pulled off, everything looked ok, so they started using it again, so now there's almost a week's worth of new data in it.
The odd thing is all new data going into this "corrupt" table is fine, it's just the data that originally had problems that is causing the problem.
If I copy the visible data from the the "corrupted" table, drop the table, recreate the table and reinsert the good data, do you think that could fix the problem?
September 27, 2006 at 10:51 am
Have you run a DBBCC CHECKDB?
If so what information does it return regarding this table?
Richard
September 27, 2006 at 12:18 pm
I've tried running DBCC CHECKDB and DBCC CHECKDB WITH ALL_ERRORMSGS. No errors are shown... This is what it returns:
DBCC results for 'OurDB'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'tblX'.
There are 36316 rows in 653 pages for object "tblX".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'OurDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 5, 2006 at 2:24 am
Hi,
Try to DTS the data out to another table or BCP the data out. If that still doesn't read the last 16 columns out, try dropping the index and then doing it. If that doesn't work, then restore an old copy of the table into another database and rebuild the bad data from the restore. You will probably still have a few corrupted records from transactions between the periods.
Hope this helps.
Fiaz
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply