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 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?
>
>|||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...
> 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 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?
>>|||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.
>