Sunday, March 11, 2012

Error 605 when checkin a 6.5 db

I am getting an Error 605:
'Attempt to fetch logical page <page#> in database <db> belongs to object 0, not to object <table1>'

Does anyone know if this is data corruption or if it is a problem worse than that, and how to begin to address it?

Any help will be much appreciated.
Thank youDo you know what is causing this error ? When is this error generated ?|||Generally that indicates data and / or index corruption, from your specific message most probably corruption in object <table1>.

Q1 Have you at least run dbcc checktable ('table1') WITH ALL_ERRORMSGS on table1?

Normally one would also subsequently perform a dbcc checkdb ('DBName') WITH ALL_ERRORMSGS to make sure there are not issues with / in other objects. (Depending on the results you may need to either perform repairs, restore from backup dumps, etc.)|||Which service pack do you have installed ? There are other less common causes for error 605 - that is why I asked when does the error occur. If it is related to system table(s), you can use sp_fixindex to correct index pages - if the errors exist within data pages you may have to restore from a backup. Otherwise, if you think that it is a corruption then run dbcc checkdb/checkalloc.|||A good point, (certainly if checktable, checkdb, etc. have already indicated that the objects are error free? then the cause(s) likely lie elsewhere).

In any case, more information in such circumstances, (what has been done, some level of system information, etc.), usually makes it much easier to diagnose issue(s) and give you more useful / helpful feedback.|||Thank you all for your replies, we ended up creating a new table and inserting (by chunks) into the new table until the process hung, then trying to find the problem record. Once pinpointed, we selected everything before and after into a new table, dropped the old table and renamed the new table.|||You are welcome.

It is fortunate that not much data was corrupt; Sql Server version. 6.5 supported table level restores (something you may wish to consider implementing to save time, if more row corruption issues arise in the table again). You may also wish to consider periodically running a dbcc checkdb ('DBName') WITH ALL_ERRORMSGS to make sure there are not similar issues with other objects.|||Happy to help.

I remember a time with sql server 7 a similar situation. We had 1 bad pointer (text field) and had to isolate it just as you did. Replication kept bombing during the bcp. It was difficult to find since the table had hundred of thousands of records and the bad field was near the end - replication did not specify a problem with the bcp and as you watched bcp run it appeared it had processed all the records (since the corruption was at the end).

No comments:

Post a Comment