| Author |
Topic |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2005-08-01 : 20:56:33
|
| Hi all, I'm not too sure if this should be in this forum or in the admin one, but since DBCC is required to fix this error, so I might as well put it here. What I'm having is this error msg:Attempt to fetch logical page in database 'DBName' belongs to object 'TableName', not to object 'TableName'.Error: 605, Severity: 21, Stat: 1I've checked BOL and it said that DBCC CheckTable on the 2nd table will fix this issue (or by using CheckDB), that is fine but what I want to know is why is this happening, and how to prevent it, I have a cluster server holding this DB, we have disabled write cache and we have tried to fail over to another node, but this issue does happen again (more often now), its running on SQL 2000 with sp4. Anyone got any ideas of why this is happening? and how to prevent it?Thanks |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-01 : 22:11:51
|
quote:
Originally posted by Westley
Hi all, I'm not too sure if this should be in this forum or in the admin one, but since DBCC is required to fix this error, so I might as well put it here. What I'm having is this error msg:Attempt to fetch logical page in database 'DBName' belongs to object 'TableName', not to object 'TableName'.Error: 605, Severity: 21, Stat: 1I've checked BOL and it said that DBCC CheckTable on the 2nd table will fix this issue (or by using CheckDB), that is fine but what I want to know is why is this happening, and how to prevent it, I have a cluster server holding this DB, we have disabled write cache and we have tried to fail over to another node, but this issue does happen again (more often now), its running on SQL 2000 with sp4. Anyone got any ideas of why this is happening? and how to prevent it?Thanks
You've come to the right place  Are you using either read-uncommited isolation level or the NOLOCK locking hint on any queries? Either of these will cause this error as you're basically doing dirty reads. Is the 2nd table name usually the same?Have you run CHECKDB after getting one of these errors?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-02 : 01:20:54
|
| Thanks Paul, There aren't any NOLOCK hint on any queries, but as you mention, that shouldn't cause this error anyway. The 2nd table name is usually the same, we do rename that table into other name and create a new table as that name pretty often (around once a day) ie, TableA will get rename to TableA2 and recreate TableA using the same structure. I hope this doesn't have any issue it, I don't have the CheckDB error anymore as we kind of fixed it by running DBCC DBREINDEX. Not too sure what actually caused it and really like to prevent this happening again :) |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-02 : 12:09:49
|
quote:
Originally posted by Westley
Thanks Paul, There aren't any NOLOCK hint on any queries, but as you mention, that shouldn't cause this error anyway. The 2nd table name is usually the same, we do rename that table into other name and create a new table as that name pretty often (around once a day) ie, TableA will get rename to TableA2 and recreate TableA using the same structure. I hope this doesn't have any issue it, I don't have the CheckDB error anymore as we kind of fixed it by running DBCC DBREINDEX. Not too sure what actually caused it and really like to prevent this happening again :)
Actually I said that NOLOCK can cause 605s. Did you check for read uncommitted?Renaming tables as you describe should not cause any problems.Without the CHECKDB output we're stuck - can't tell you how to prevent the problem if I don't know what it is. Keep running regular (daily) CHECKDBs and let me know if it happens again.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-02 : 19:09:30
|
| Thanks, will get the dbcc output next time, I'm sure it'll come up again soon. |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-02 : 19:20:57
|
| Paul, I've found someone actually got the DBCC Checktable for the 2nd table:Server: Msg 2533, Level 16, State 1, Line 1Table error: Page (7:167537) allocated to object ID 592773219, index ID 2 was not seen. Page may be invalid or have incorrect object ID information in its header.Server: Msg 8976, Level 16, State 1, Line 1Table error: Object ID 592773219, index ID 2. Page (7:167537) was not seen in the scan although its parent (7:167409) and previous (7:167536) refer to it. Check any previous errors.Server: Msg 8978, Level 16, State 1, Line 1Table error: Object ID 592773219, index ID 2. Page (7:167538) is missing a reference from previous page (7:167537). Possible chain linkage problem.DBCC results for 'tbl_TransferHoldings'.There are 1850878 rows in 15947 pages for object 'tbl_TransferHoldings'.CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'tbl_TransferHoldings' (object ID 592773219).repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (tbl_TransferHoldings ).Don't think that is the full error, but at least a starting point? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-02 : 19:27:35
|
| This looks like the full output. Page 7:167537 has something wrong with it. Can you do a DBCC PAGE (dbname, 7, 167537, 1) and post the page header portion only (which won't contain any private info)? I'd like to see what object it thinks its allocated to, and whether the PFS page thinks its allocated.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-02 : 23:54:55
|
| Can't really get the result of it since we already ran the reindex, and it fixed it somehow, will wait until next time when we have that issue, then will get all the logs saved, will ask for help then..:)Thanks |
|
|
|
rkirk
Starting Member
10 Posts |
Posted - 2005-08-10 : 14:03:58
|
| Question regarding error 605 and read uncommitted transactions. Don't these have a severity level of 12 rather than 21? (according to MS KB235880)Just curious because we are seeing 605 level 21 errors and use (NOLOCK) quite a bit. Could this be the cause of our problems? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-10 : 14:17:48
|
quote:
Originally posted by rkirk
Question regarding error 605 and read uncommitted transactions. Don't these have a severity level of 12 rather than 21? (according to MS KB235880)Just curious because we are seeing 605 level 21 errors and use (NOLOCK) quite a bit. Could this be the cause of our problems?
Yes, you're right - I missed that in the original post. All 605s raised during NOLOCK scans or when READ_UNCOMMITTED isolation level is specified are sev 12. Real problems are sev 21. Can you run CHECKDB WITH NO_INFOMSGS and start a new thread so we can discuss the results (if you want)?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-11 : 01:10:04
|
| Hi guys, We just had that issue again today, background again, sql 2k + sp4, running on cluster, what I did over the weekends was failover to the other node, so I can make sure that its not the hardware having issue, runs fine for a few days then come back up.....this time is funny, where i got that error in the log, I go in and do a dbcc checkdb, and checktable, no errors were found at all, and I just run that DTS (was having a dts to transfer some data in the morning) again, and it just work.....looking like it just at the time it somehow failed, I talked to the other dbas, and they are saying they read somewhere saying the 605 issue might involve some data is there and another process is trying to read it and the timing is too fast or something, which they can't really remember.is there any other thing i can do? as it just seems to keep up pretty often now....thanks |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-11 : 11:55:52
|
| Do you have write-caching enabled on the drives? If so, turn it off and the problem should go away.If not, could you do a DBCC PAGE on the page ID in the 605 message and post the header portion of the output? If its a non-clustered index again (as it was before in the CHECKDB output), you may be hitting a bug. I can do some more searching internally and let you know if there's anything known you're running into. If I can't find anything, you'll need to call PSS to help out as we're out of options for net-based help I'm afraid.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-11 : 20:25:15
|
| Thanks Paul, We got it this morning again, but that guy forgot to capture the dbcc page info :( We notice that it was causing by a job that call in the morning, which contain a dts (transferring some data from other source to sql) then run a store proc. We did get the other info as follow:DBCC Checktable:Server: Msg 8978, Level 16, State 1, Line 1Table error: Object ID 2048778406, index ID 2. Page (3:76984) is missing a reference from previous page (6:87487). Possible chain linkage problem.Server: Msg 2533, Level 16, State 1, Line 1Table error: Page (6:87487) allocated to object ID 2048778406, index ID 2 was not seen. Page may be invalid or have incorrect object ID information in its header.Server: Msg 8976, Level 16, State 1, Line 1Table error: Object ID 2048778406, index ID 2. Page (6:87487) was not seen in the scan although its parent (6:87330) and previous (6:87486) refer to it. Check any previous errors.DBCC results for 'tbl_Transfer'.There are 1862669 rows in 16066 pages for object 'tbl_Transfer'.CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'tbl_Transfer' (object ID 2048778406).repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (CC.dbo.tbl_Transfer ).From SQL Server Error:objID is/should be (1600776810/2048778406)1600776810 object name: NULL2048778406 object name: tbl_Transferwe found the indid 2 is a non-clustered index which contains only 1 key which is a datetime, what we did was run dbcc dbindex on that index, and dbcc checktable returns no error afterwards (seems to fix it), so we rerun the store proc again, everything seems fine again.We also found that it was causing from a store proc which calling a insert statement, which having a where cause (comparing the date) on that datetime column for that index.Does that sounds like a bug? or we still needs to have the dbcc page info to get down to the bottom of this?Thanks |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-11 : 21:05:54
|
| This looks like a bug and you should call PSS to help you out. Can you let them know that I'd like to be internally kept in the loop of the investigation?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-11 : 21:14:44
|
| Sure, I'll need to find out how I can rise that through the other teams.....I'm actually not in the US, so will that make any diff to let them know to keep u in the loop? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-11 : 21:33:35
|
| Nope. Just ask whoever you eventually get through to to let me know how things go. Just to be on the safe side you could email me the SR number that they'll give you and I'll make sure I'm in the loop.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-11 : 23:59:57
|
| Sure, will do that once I got the SR number, by the way, will that be safe that I just remove that given index, and it "should" prevent this error happening? since its only got to do with that index?or even tho I remove this index, it'll just come up with another indexes? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-12 : 00:36:06
|
| I don't know - as I don't know what the bug is you've hit - although my guess would be yes.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-12 : 01:35:07
|
| cool, thanks for the help, would really like to know what really happened to it, will keep u updated once i got any news... |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-08-18 : 21:20:09
|
| Paul, I have sent you an email yesterday with the SR number, so maybe you can make sure you are in the loop of the whole thing :)Thanks |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-19 : 11:39:56
|
quote:
Originally posted by Westley
Paul, I have sent you an email yesterday with the SR number, so maybe you can make sure you are in the loop of the whole thing :)Thanks
Got it thanks - will get hooked into the loop.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Next Page
|
Attempt to fetch logical page 605