Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We run SQL Server 2000 and we get a few of these errors every night.

Could not continue scan with NOLOCK due to data movement

The query that throws this error is a large complex query that joins over a dozen tables. Our underlying data can be updated frequently.

The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints.

Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :)

PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

share|improve this question
3  
I'd drop the NOLOCK and fix the query/indexes/process. We can help of course... Also see en.wikipedia.org/wiki/Halloween_Problem –  gbn Dec 9 '11 at 9:12
4  
The possibility of error 601 is unavoidable when performing scans at READ UNCOMMITTED isolation level in any version of SQL Server. The 'solution' is either to use a different isolation level or handle the error gracefully (as you might do for a deadlock). –  Paul White Nov 30 '12 at 7:12
2  
@SQLKiwi: SQL 2012 recovers gracefully in many cases of data movement under dirty scans (continues on next page in allocation order). –  Remus Rusanu Nov 30 '12 at 11:06
1  
@RemusRusanu That's interesting to know, thanks. I assume from your wording that there are still cases in 2012 where 601 will still occur? –  Paul White Nov 30 '12 at 11:22
1  
@SQLKiwi: yes, there still are. On the good new: also cursors backed by dirty scans should handle this more gracefully. –  Remus Rusanu Nov 30 '12 at 11:40

2 Answers 2

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, but before it attempts to read the data row.

Fortunately, the fix is relatively simple: http://support.microsoft.com/kb/815008

If that doesn't work, you have the slightly more painful option of removing all your WITH (NOLOCK) hints and setting your transaction isolation level to something above READ UNCOMMITTED.

share|improve this answer
    
We're up to date with that fix - we applied the flag, restarted and we still get these errors. –  Ciaran Archer Dec 9 '11 at 9:15

During my previous experience i was also getting the same error...

"Msg 601, Level 12, State 1, Line 2 Could not continue scan with NOLOCK due to data movement."

The very cause of this is during a scan DataBase Engine goes through the records as it is stored physically. So if any concurrent process is going on that disturbs data in the physical level like Index Creation,Delete,Insert etc then scan process gets disturbed even with the use of table hints.

Solution for this is to wait till all physical level operations are done else kill the process (be careful on this).

Hop this helps

share|improve this answer
6  
-1 Abysmally bad advice. You don't even remotely understand the problem. –  Remus Rusanu Nov 30 '12 at 7:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.