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 (AT) discussions (DOT) microsoft.com> wrote

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 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 (AT) discussions (DOT) microsoft.com> 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 had similar problem yesterday with a table scan on sqlserver 2005. fixed by running the same script for few seconds and abort . then let it run normally .
it scans through all records of whole database .
stopping iis and apps access mssql didn't help.


weird fix ?

rahul (ray.777b (AT) gmail (DOT) com) writes:
The proper fix is to stop using NOLOCK, and turn on READ_COMMITTED_SNAPSHOT
to avoid locking issues in a busy environment.

Using NOLOCK means that not only can you see uncommitted data, but also fail
to read committed which can lead to considerable errors.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx