October 16, 2002 at 2:34 pm
I have been getting this message quite a bit recently and I would like to find a cause and resolution. MS says the following:
************
Error 601
Severity Level 12
Message Text
Could not continue scan with NOLOCK due to data movement.
Explanation
When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft® SQL Server™ is not able to continue the scan.
Action
This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.
********************
The action provided by MS doesn't meet my needs. We can resubmit the query, but at times, I have seen this last for several hours. Removing NOLOCK hints will not work since this is being seen through the application which has NOLOCK throughout. Has anyone had success combatting this issue?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
October 16, 2002 at 7:32 pm
We have also run into this problem. We have more than 400 or 500 stored procedures that use the NOLOCK hint on every table.
We used to see these errors more often than we do now. I think the main reason we have less of these errors now is that we don't actually delete rows from tables during our peak hours. We have a column in most of our tables called Deleted. All our stored procedures only display records where Deleted = 0.
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
October 17, 2002 at 3:53 am
Same thing here I recently had to drop 150000 rows in 4 different tables and that is when the queries with the NOLOCK hint got this error. Either you will need to prevent DELETES, and possibly UPDATES, and INSERTS where fair to large amounts of data must move when you are running these queries or for the NOLOCK hint altogether.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 25, 2002 at 10:53 am
I appreciate your responses. In general, most of the inserts, updates, deletes done in our application are going to be less than 1000 records. We do have large tables and a lot of dml activity but I usually do all bulk dml after hours. My understanding is that we see the error when doing queries rather than dml. Ofcourse, dml can be going on simultaneously while doing queries. Any other thoughts?
Thanks
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply