Could not continue scan with NOLOCK due to data movement

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://support.microsoft.com/default.aspx/kb/815008.
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!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
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>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://support.microsoft.com/default.aspx/kb/815008.
> 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!
>|||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
<KizzyGaul@.gmail.com> wrote in message
news:1167432375.684460.178510@.48g2000cwx.googlegroups.com...
>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://support.microsoft.com/default.aspx/kb/815008.
> 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!
>