Could not continue scan with NOLOCK due to data movement

Could not continue scan with NOLOCK due to data movement

Post by KizzyGau » Sun, 31 Dec 2006 07:46:15


I have a client that is continually getting this error message:

Could not continue scan with NOLOCK due to data movement

Intially they were not at SP4 of SQL Server 2000, so per this Microsoft
Document they patched the server
http://www.yqcomputer.com/

Even after patching, they are continuing to get the error.

The article also mentions a trace flag, but I don't think that flag
should apply. Our software is not trying to "READ UNCOMMITTED"
isolation levels, we are providing a WITH NO LOCK optimizing hint on
the SQL statement.

Any help would be appreciated. If you need additional information, let
me know.

Thank you!
 
 
 

Could not continue scan with NOLOCK due to data movement

Post by Andrew J. » Sun, 31 Dec 2006 08:44:47

That is usually due to the fact they are running in Read Uncommitted
isolation level or are using the NOLOCK hint. If they are reading a page /
row and another user deletes that row or splits that page they get hosed.
This is usually rare unless you do a lot of scanning which typically
indicates lack of proper tuning and indexes.

--
Andrew J. Kelly SQL MVP

 
 
 

Could not continue scan with NOLOCK due to data movement

Post by Geoff N. H » Sun, 31 Dec 2006 12:20:36

Read Uncommitted and WITH NOLOCK are equivalent. WITH NOLOCK sets the
isolation level for the individual query while READ UNCOMMITTED does the
same thing for either the connection or the entire database, depending on
where it is used. You are likely using a very active transactional system
to support an analysis type query. The underlying transactional changes
cause the large aggregations or intermediate result sets to fail. You can
try using a replicated or log shipped copy as your source or upgrade to SQL
2005 and try using Snapshot isolation.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP