November 6, 2003 at 10:10 am
I am placing this here because I don't think I get the error because the code is run from a DTS package.
When a DTS package executes code stored in a variable I get the following error:
Attempt to fetch logical page (1:6969) in database 'dbMutualFunds' belongs to object '1406784218', not to object 'Holdings_New'
The dynamic code being executed creates a clustered index on Holdings_New.
I have gotten this error 3 times between 4 Oct 2003 and today. As far back as July 2003 this same package has been running and I didn't get the error.
There is no replication on this database and the only inserts and object manipulations done to this database are while this package is running. The table Holdings_New did exist after the package failed and stopped.
At the time the create clustered index statement is executed no object changes occur and no datapumps are running.
The only thing I can think of is that the autoshrink is moving objects and interfering with the execution of the create clustered index command. I found this about autoshrink in BOL:
SQL Server 2000 autoshrinks databases that have a large amount of free space.
The server checks the space usage in each database periodically. If a database is found with a lot of empty space and it has the autoshrink option set to true, SQL Server reduces the size of the files in the database.
If there are used pages in the part of the file being released, SQL Server first relocates the pages to the part being retained.
It is possible that there is a lot of free space since I pump updated data to _New tables, then rename the tables, and then drop the _Old tables.
In late September 2003 we upgraded to SP3, so if a change was made to the autoshrink portion then that explains why I haven't had problems before October 2003.
Is there anything else that could cause this error? Does my logic sound right?
I don't get the error all the time, in fact today I simply reran the DTS package without changing it and didn't get the error. I even see one day (11th of October) that it ran the first time without problems.
I am thinking of switching the autoshrink off while the package runs and then turning it back on once finished. Another option may be to simply shut autoshrink off and shrink the database in the last step of the package since there will not be another time when the autoshrink will need to run.
I am hoping that someone else has seen this error before and can confirm that I have identified the problem correctly. If no one has seen it before then I hope I have given enough info that this post will be useful if seen by someone with this error in the future.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 6, 2003 at 10:20 am
the error indicates database corruption.
see error 605 in BOL .
HTH
Paul
November 7, 2003 at 8:36 am
Thank you very much for your response. The error number is never included with the failure logged by the DTS package. I read error 605 and believe the info was useful. It appears the error is temporary (microsoft calls it transient). I come to such a conclusion since DBCC CHECKTABLE and DBCC CHECKDB both come back clean.
My guess is pages are getting moved (due to shrink database) while the index is being created. The object gets renamed after all indexes are created and all datapumps for all tables completes.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply