| Author |
Topic |
|
shahgols
Starting Member
9 Posts |
 Posted - 2005-10-28 : 14:49:54
|
| Hi everyone, I asked this question in another forum, but nobody was able to help me. I hope that someone here can help me. I was reading through what BOL says about this error and I had a question about this part:If instead of an object name the error displays a number greater than 0, it means that an attempt was made to reference an object ID that does not exist in a system table for that object.Does this mean an object such as a temporary object that was created through our code and then dropped later in the code? What kind of object are they talking about? We are getting this error very frequently in the past couple of weeks and the error is always showing an object ID instead of a name and that is why I am curious to know what the object can be. The 605 error text I get is:Msg 605, Sev 21: Attempt to fetch logical page (1:18506) in database 'proddb' belongs to object '1852795252', not to object 'proddb_table1'. [SQLSTATE HY000]Thanks. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-28 : 16:02:16
|
| Does dbcc checkdb show any errors?The only objects that would give this error would be tables and indexes, as Procedures and Views do not take up data pages. So for your temporary objects, are you creating temp tables (#tablename) or table variables in your stored procedure code, or are you actually creating tables in your database that are used for a set amount of time? |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-28 : 16:02:22
|
quote:
Originally posted by shahgols
Hi everyone, I asked this question in another forum, but nobody was able to help me. I hope that someone here can help me. I was reading through what BOL says about this error and I had a question about this part:If instead of an object name the error displays a number greater than 0, it means that an attempt was made to reference an object ID that does not exist in a system table for that object.Does this mean an object such as a temporary object that was created through our code and then dropped later in the code? What kind of object are they talking about? We are getting this error very frequently in the past couple of weeks and the error is always showing an object ID instead of a name and that is why I am curious to know what the object can be. The 605 error text I get is:Msg 605, Sev 21: Attempt to fetch logical page (1:18506) in database 'proddb' belongs to object '1852795252', not to object 'proddb_table1'. [SQLSTATE HY000]Thanks.
Sounds like your database is corrupted. If you're not a DBA and don't have one, you might want to hire one for this issue.  [url]http://www.mssqlcity.com/FAQ/Trouble/er605.htm[/url]Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
shahgols
Starting Member
9 Posts |
Posted - 2005-10-28 : 16:20:56
|
| Hi there, thanks for your reply.We make a lot of use of table variables in our code. Could they be the objects that the BOL refers to? I am a DBA and have restored the database, it is up and running find now. But this is a recurring issue for us and I am almost at the end of my options. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-10-28 : 16:49:43
|
| No. You've got real database corruption.Please post the results of DBCC CHECKDB (proddb) WITH ALL_ERRORMSGS, NO_INFOMSGSI take it you have up-to-date backups?Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-10-28 : 16:51:44
|
quote:
Originally posted by shahgols
Hi there, thanks for your reply.We make a lot of use of table variables in our code. Could they be the objects that the BOL refers to? I am a DBA and have restored the database, it is up and running find now. But this is a recurring issue for us and I am almost at the end of my options.
I personally do not like table variables. They throw off your execution plans and make it very hard to trouble shoot performance issues. I feel the same about table functions. TempDB is the way to go in my opinion. I've seen tons of KB articles about problems that are caused by the table variables too. Thats not the problem here but I would work on redoing the code to remove them. That's just my opinion. I'm sure you'll hear a few different opinions going both ways on using those...Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
shahgols
Starting Member
9 Posts |
Posted - 2005-10-28 : 17:38:56
|
| Paul, Thank you for your offer to help. I have already restored the database and it is up and running currently. All is fine. However, this is the third time that this has happened in the past week alone, so I am sure we are due for another one anytime now. I'll post the results of checkdb if and when this happens again. For now we are trying to get to the bottom of why this is happening. |
|
|
|
shahgols
Starting Member
9 Posts |
Posted - 2005-10-28 : 19:00:22
|
| Paul, Can you please tell me what kind of objects these numbers (1852795252) refer to? Is it the table variables?Msg 605, Sev 21: Attempt to fetch logical page (1:18506) in database 'proddb' belongs to object '1852795252', not to object 'proddb_table1'. [SQLSTATE HY000] |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-31 : 09:42:44
|
| These would refere to objects in proddb. Temporary tables allocate only in tempdb. I think (though I could be about to get schooled on the matter) table variables allocate space in tempdb as well, though they are generally kept in memory. At any rate, table variables will not allocate space in a user database. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-10-31 : 13:36:49
|
quote:
Originally posted by shahgols
Paul, Can you please tell me what kind of objects these numbers (1852795252) refer to? Is it the table variables?Msg 605, Sev 21: Attempt to fetch logical page (1:18506) in database 'proddb' belongs to object '1852795252', not to object 'proddb_table1'. [SQLSTATE HY000]
Can you post the output of:select name, type from proddb.sysobjects where id=1852795252Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
shahgols
Starting Member
9 Posts |
Posted - 2005-10-31 : 19:41:49
|
| Hi Paul,I always run such query to find the object, and always I get null. Everytime we get this problem, it has been the case that the object does not exist. That is why I am wondering what kind of object it might be that is getting such problems. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-10-31 : 20:37:08
|
| Have you ran Paul's query in tempdb? Also, are there any other errors in the SQL Server Error logs, server application log, or server system log?You said you will run CHECKDB next time this error occurs. Why don't you run it now? Matter of fact, run it twice a day between now and the time it breaks if it doesn't hurt your systems performance. Out of curiosity, what do the read and write queue lengh max and averages look like on the drive your tempdb is on?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
shahgols
Starting Member
9 Posts |
Posted - 2005-11-01 : 11:53:32
|
| Derrick,Once I ran that query in tempdb and got null as well.I ran the counters for a couple of hours. The avg/max read and write are all near 0, if not 0. Thanks. |
|
|
|
|
Error 605