Could not continue scan with NOLOCK due to data movement.

I received the error message
Could not continue scan with NOLOCK due to data movement.
when I run the following SQL
update statistics PCR_Value with fullscan
I don't get any error if I run
update statistics PCR_Value with sample 75 percent
or any percentage smaller than 75. But if I go over 75 percent, then I get
the "Could not continue scan with NOLOCK due to data movement." I've run
dbcc checktable and receive no errors. Any suggestions?
Try running the stats update during a period of no/low activity. To improve
performance I would guess that process does not take a table lock and thus
can get 'confusled' when it tries to hit data that has changed underneath of
it.
Also, if you REALLY need to make it happen, what about opening a
transaction, doing a select top 1 * using an exclusive table lock hint,
running the update stats in the same tran, then committing the tran? Not
sure if the update stats would even be allowed to run there though. NOTE
that you would lock out ALL access to the table during the time this was
going on, even if it took hours!!
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>I received the error message
> Could not continue scan with NOLOCK due to data movement.
> when I run the following SQL
> update statistics PCR_Value with fullscan
> I don't get any error if I run
> update statistics PCR_Value with sample 75 percent
> or any percentage smaller than 75. But if I go over 75 percent, then I
> get
> the "Could not continue scan with NOLOCK due to data movement." I've run
> dbcc checktable and receive no errors. Any suggestions?
|||Kevin,
Thanks for the advice. My main concern about getting this error is what
caused it. Updating statistics of large tables in the database is part of a
server installation program and our installer made sure that all of the
applications on the server were terminated before the installation program
started. We have repeated this process twice on the server and encountered
the same error. The same migration process was executed for users at other
sites with a lot more rows in the same table and the process ran without
error. Could this be a SQL bug for MS SQL 2000?
"Kevin G. Boles" wrote:

> Try running the stats update during a period of no/low activity. To improve
> performance I would guess that process does not take a table lock and thus
> can get 'confusled' when it tries to hit data that has changed underneath of
> it.
> Also, if you REALLY need to make it happen, what about opening a
> transaction, doing a select top 1 * using an exclusive table lock hint,
> running the update stats in the same tran, then committing the tran? Not
> sure if the update stats would even be allowed to run there though. NOTE
> that you would lock out ALL access to the table during the time this was
> going on, even if it took hours!!
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>
>
|||I suppose it could be a bug somewhere. But I think it is caused due to some
form of DML during data read operations. Are you absolutely certain there
was NO DML activity (even from your own package/installer thing) concurrent?
Are you also certain it was an update statistics run?
See http://support.microsoft.com/kb/815008 for a known bug which returns
this error message. Perhaps that will help.
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <ChingjuLee@.discussions.microsoft.com> wrote in message
news:4E41D7B0-66E0-4B6D-9F15-1BDAC9C7EAF1@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Thanks for the advice. My main concern about getting this error is what
> caused it. Updating statistics of large tables in the database is part of
> a
> server installation program and our installer made sure that all of the
> applications on the server were terminated before the installation program
> started. We have repeated this process twice on the server and
> encountered
> the same error. The same migration process was executed for users at
> other
> sites with a lot more rows in the same table and the process ran without
> error. Could this be a SQL bug for MS SQL 2000?
> "Kevin G. Boles" wrote:
|||I am pretty sure the UPDATE STATISTICS with full scan caused the error
message because I can reproduce the exact error using ISQL on my test machine
when I restored the database from the user's site. I can eliminate the error
if I run UPDATE STATISTICS with 75 or lower percent. I read the article
before. The resolution was to install the latest service pack. The user,
and my test machine both have SQL 2000 SP4 installed. So still not sure what
caused the problem.
"Kevin G. Boles" wrote:

> I suppose it could be a bug somewhere. But I think it is caused due to some
> form of DML during data read operations. Are you absolutely certain there
> was NO DML activity (even from your own package/installer thing) concurrent?
> Are you also certain it was an update statistics run?
> See http://support.microsoft.com/kb/815008 for a known bug which returns
> this error message. Perhaps that will help.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>

Could not continue scan with NOLOCK due to data movement.

I received the error message
Could not continue scan with NOLOCK due to data movement.
when I run the following SQL
update statistics PCR_Value with fullscan
I don't get any error if I run
update statistics PCR_Value with sample 75 percent
or any percentage smaller than 75. But if I go over 75 percent, then I get
the "Could not continue scan with NOLOCK due to data movement." I've run
dbcc checktable and receive no errors. Any suggestions?Try running the stats update during a period of no/low activity. To improve
performance I would guess that process does not take a table lock and thus
can get 'confusled' when it tries to hit data that has changed underneath of
it.
Also, if you REALLY need to make it happen, what about opening a
transaction, doing a select top 1 * using an exclusive table lock hint,
running the update stats in the same tran, then committing the tran? Not
sure if the update stats would even be allowed to run there though. NOTE
that you would lock out ALL access to the table during the time this was
going on, even if it took hours!!
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>I received the error message
> Could not continue scan with NOLOCK due to data movement.
> when I run the following SQL
> update statistics PCR_Value with fullscan
> I don't get any error if I run
> update statistics PCR_Value with sample 75 percent
> or any percentage smaller than 75. But if I go over 75 percent, then I
> get
> the "Could not continue scan with NOLOCK due to data movement." I've run
> dbcc checktable and receive no errors. Any suggestions?|||Kevin,
Thanks for the advice. My main concern about getting this error is what
caused it. Updating statistics of large tables in the database is part of a
server installation program and our installer made sure that all of the
applications on the server were terminated before the installation program
started. We have repeated this process twice on the server and encountered
the same error. The same migration process was executed for users at other
sites with a lot more rows in the same table and the process ran without
error. Could this be a SQL bug for MS SQL 2000?
"Kevin G. Boles" wrote:

> Try running the stats update during a period of no/low activity. To impro
ve
> performance I would guess that process does not take a table lock and thus
> can get 'confusled' when it tries to hit data that has changed underneath
of
> it.
> Also, if you REALLY need to make it happen, what about opening a
> transaction, doing a select top 1 * using an exclusive table lock hint,
> running the update stats in the same tran, then committing the tran? Not
> sure if the update stats would even be allowed to run there though. NOTE
> that you would lock out ALL access to the table during the time this was
> going on, even if it took hours!!
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ching-ju Lee" <Ching-ju Lee@.discussions.microsoft.com> wrote in message
> news:6AEF167B-F5D9-43B9-88CD-1C02A74130DA@.microsoft.com...
>
>|||I suppose it could be a bug somewhere. But I think it is caused due to some
form of DML during data read operations. Are you absolutely certain there
was NO DML activity (even from your own package/installer thing) concurrent?
Are you also certain it was an update statistics run?
See http://support.microsoft.com/kb/815008 for a known bug which returns
this error message. Perhaps that will help.
TheSQLGuru
President
Indicium Resources, Inc.
"Ching-ju Lee" <ChingjuLee@.discussions.microsoft.com> wrote in message
news:4E41D7B0-66E0-4B6D-9F15-1BDAC9C7EAF1@.microsoft.com...[vbcol=seagreen]
> Kevin,
> Thanks for the advice. My main concern about getting this error is what
> caused it. Updating statistics of large tables in the database is part of
> a
> server installation program and our installer made sure that all of the
> applications on the server were terminated before the installation program
> started. We have repeated this process twice on the server and
> encountered
> the same error. The same migration process was executed for users at
> other
> sites with a lot more rows in the same table and the process ran without
> error. Could this be a SQL bug for MS SQL 2000?
> "Kevin G. Boles" wrote:
>|||I am pretty sure the UPDATE STATISTICS with full scan caused the error
message because I can reproduce the exact error using ISQL on my test machin
e
when I restored the database from the user's site. I can eliminate the erro
r
if I run UPDATE STATISTICS with 75 or lower percent. I read the article
before. The resolution was to install the latest service pack. The user,
and my test machine both have SQL 2000 SP4 installed. So still not sure wha
t
caused the problem.
"Kevin G. Boles" wrote:

> I suppose it could be a bug somewhere. But I think it is caused due to so
me
> form of DML during data read operations. Are you absolutely certain there
> was NO DML activity (even from your own package/installer thing) concurren
t?
> Are you also certain it was an update statistics run?
> See http://support.microsoft.com/kb/815008 for a known bug which returns
> this error message. Perhaps that will help.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>

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.googlegro ups.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.googlegro ups.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!
>

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!
>