February 5, 2002 at 9:33 am
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not continue scan with NOLOCK due to data movement.
We are using SQL server 7.0 with Service pack 3. This is one error that we get quite often. According to Microsoft this problem should have been fixed in SP3. Any other suggestions or things I can try to fix this problem
February 5, 2002 at 10:42 am
Are you sure nothing got changed after the sp got applied? Might be worth re-applying just to make sure
Andy
February 5, 2002 at 10:46 am
Can you provide you code for the connection minus sensative stuff. And the query and recordset object.
February 5, 2002 at 11:48 am
I don't think anything has changed. But what kind of changes are you referring to. I have many stored procedures that gets changed regularly.
Are you aware of any reason because of which we can get these errors. As I am not aware of any reason in SQL server 7.0 with SP3 we should get that error message.
Any suggestions or help would be really appreciated.
quote:
Are you sure nothing got changed after the sp got applied? Might be worth re-applying just to make sureAndy
February 5, 2002 at 11:52 am
The stored procedures I am talking about are very big stored procedures using lot of custom tables.
Its not that I get this error everytime I use that stored procedure. I get this error message in different stored procedures accessing the tables that can be updated or records can be inserted by the users of our website.
Can you think of any reasons why we can get these errors in SQL 7.0 with SP3 on it.
quote:
Can you provide you code for the connection minus sensative stuff. And the query and recordset object.
February 5, 2002 at 12:44 pm
Was digging around MSDN found this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_23ld.asp
Basically says the error happens because underlying data page moves. Do you have a link to where MS says they fixed the bug?
Andy
February 5, 2002 at 12:47 pm
Ok I was thinking maybe in an app. However are any of the Stored Procedures using cursors. I have seen this on a few cases where the type of cursor opened does not support NOLOCK in a query if the data is changed but I am sorry I don't have SQL installed here at the moment to check Books Online. Also do you get an error number?
February 5, 2002 at 2:05 pm
Its not exactly the same thing tht you posted but these are some of them I looked at :
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q275427
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q268081
I guess they have not fixed the problem yet. Either I have to remove the NoLock which I can't or live with this error.
I am not allowing users to delete any data from the tables. I am just allowing them to update or insert. Should I still be getting this error. Any details on what is meant by
"page at the current position of the scan to be deleted"
Any suggestions to stop or reduce these errors.
quote:
Was digging around MSDN found this:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_23ld.asp
Basically says the error happens because underlying data page moves. Do you have a link to where MS says they fixed the bug?
Andy
February 5, 2002 at 4:57 pm
First be aware that nolock hint allows dirty reads. You can also hint an index e.g.
select * from tbl (nolock, index = IX_tbl_fld2)
This could help speed things up. If deletes are in progress, you may still get the error.
Try also dropping the nolock hint. It will be slower and you may get blocked but you will not have the dirty read problem.
February 5, 2002 at 5:01 pm
Error Number is 601.
We are not using cursors.
quote:
Ok I was thinking maybe in an app. However are any of the Stored Procedures using cursors. I have seen this on a few cases where the type of cursor opened does not support NOLOCK in a query if the data is changed but I am sorry I don't have SQL installed here at the moment to check Books Online. Also do you get an error number?
February 5, 2002 at 5:07 pm
Yes, I am aware that NoLock allows dirty reads. Dirty reads are not a huge problem on your website. We would like to allow our users much faster access. Our data is not such that many people can change same records. Only few people can change the records. Mostly its just users who created that record can change it. All the deletes are soft deletes so are just Updates.
We already have hints for indexes. As the updates on these tables are very common we see a significant speed increase even less or no timeouts with NoLock hint in there.
Its just that we get these errors few times a day. And I was trying to figure out if there is any solution for it.
quote:
First be aware that nolock hint allows dirty reads. You can also hint an index e.g.select * from tbl (nolock, index = IX_tbl_fld2)
This could help speed things up. If deletes are in progress, you may still get the error.
Try also dropping the nolock hint. It will be slower and you may get blocked but you will not have the dirty read problem.
February 6, 2002 at 8:00 am
When you are talking about data movement, how much data?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 6, 2002 at 10:18 am
There is approx 3 million records in one of the table.
quote:
When you are talking about data movement, how much data?K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
February 6, 2002 at 12:53 pm
At this point I would be curious about wheat the SP does internally. The answer has to lie in the complexity of the procedures. You may need to rethink them through and make sure they are as optimal as possible.
February 6, 2002 at 1:46 pm
There are 3 million records in the table, but:
a) How often does "data movement" happen?
b) How many rows are affected?
c) What is the average duration?
I've run into this problem at times when doing large imports of data in SQL Server 7, and I'm checking the count to see how things are progressing.
If you can't get rid of the NOLOCK then what you may have to do is trap for the error and retry on the client side.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply