Could not continue scan with NOLOCK due to data movement 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
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.

Craig Freedman gives a good write up here

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.